You are basically trying to group values where the individual values are different but each in itself accumulated in stead of
accumulated for the grouping.
Just move the scope of the grouping and use Nulls in stead of 0's, like this:
SELECT stats.which_year AS year,
SUM(CASE WHEN stats.which_month = 1 THEN stats.quantity ELSE NULL END) AS
gen,
SUM(CASE WHEN stats.which_month = 2 THEN stats.quantity ELSE NULL END) AS
feb,
SUM(CASE WHEN stats.which_month = 3 THEN stats.quantity ELSE NULL END) AS
mar,
SUM(CASE WHEN stats.which_month = 4 THEN stats.quantity ELSE NULL END) AS
apr,
SUM(CASE WHEN stats.which_month = 5 THEN stats.quantity ELSE NULL END) AS
mag,
SUM(CASE WHEN stats.which_month = 6 THEN stats.quantity ELSE NULL END) AS
giu,
SUM(CASE WHEN stats.which_month = 7 THEN stats.quantity ELSE NULL END) AS
lug,
SUM(CASE WHEN stats.which_month = 8 THEN stats.quantity ELSE NULL END) AS
ago,
SUM(CASE WHEN stats.which_month = 9 THEN stats.quantity ELSE NULL END) AS
sett,
SUM(CASE WHEN stats.which_month = 10 THEN stats.quantity ELSE NULL END) AS
ott,
SUM(CASE WHEN stats.which_month = 11 THEN stats.quantity ELSE NULL END) AS
nov,
SUM(CASE WHEN stats.which_month = 12 THEN stats.quantity ELSE NULL END) AS
dic,
stats.test_id AS test_id
FROM stats
WHERE stats.test_id = 420
GROUP BY which_year
Have a great day!
Ryan
On 2013/12/21 10:42, Giuseppe Costanzi wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
