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

Reply via email to