HI all,
I've a table such
CREATE TABLE 'stats' (
'stat_id' INTEGER PRIMARY KEY,
'test_id' INTEGER,
'quantity' INTEGER,
'which_month' INTEGER,
'which_year' INTEGER,
)
and I need to extract data with somenthing like
SELECT
stats.which_year AS year,
CASE WHEN stats.which_month = 1 THEN SUM(stats.quantity) ELSE 0 END AS
gen,
CASE WHEN stats.which_month = 2 THEN SUM(stats.quantity) ELSE 0 END AS
feb,
CASE WHEN stats.which_month = 3 THEN SUM(stats.quantity) ELSE 0 END AS
mar,
CASE WHEN stats.which_month = 4 THEN SUM(stats.quantity) ELSE 0 END AS
apr,
CASE WHEN stats.which_month = 5 THEN SUM(stats.quantity) ELSE 0 END AS
mag,
CASE WHEN stats.which_month = 6 THEN SUM(stats.quantity) ELSE 0 END AS
giu,
CASE WHEN stats.which_month = 7 THEN SUM(stats.quantity) ELSE 0 END AS
lug,
CASE WHEN stats.which_month = 8 THEN SUM(stats.quantity) ELSE 0 END AS
ago,
CASE WHEN stats.which_month = 9 THEN SUM(stats.quantity) ELSE 0 END AS
sett,
CASE WHEN stats.which_month = 10 THEN SUM(stats.quantity) ELSE 0 END AS
ott,
CASE WHEN stats.which_month = 11 THEN SUM(stats.quantity) ELSE 0 END AS
nov,
CASE WHEN stats.which_month = 12 THEN SUM(stats.quantity) ELSE 0 END AS
dic,
stats.test_id AS test_id
FROM stats
WHERE stats.test_id = 420
GROUP BY which_year
that return this
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "71769" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "68972" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "65075" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "50605" "420"
if I add which_month in th e GROUP BY clause I've
"2009" "5994" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "7046" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "5947" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "6471" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "6027" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "5841" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "3352" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "6564" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "7075" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6283" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5057" "420"
"2010" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "6201" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "6890" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "5907" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "6246" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "5667" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "5185" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "3269" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "5963" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6520" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6162" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "4850" "420"
"2011" "5888" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "6027" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "6756" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "5889" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "6276" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "5985" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "4968" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "3159" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "5562" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5957" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5131" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "3477" "420"
"2012" "4949" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "4790" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "5608" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "4337" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "4846" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "4739" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "3887" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "2412" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "4404" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "4542" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "3622" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "2469" "420"
finally the question is:
How I can keeping months and years in the same row?
what i mistake in my query?
thanks for any suggestions?
beppe
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
