I know that there are a lot of question like this:
GROUP BY CASE expression with aggregate function
but I've tried doing:
with T as (select col1 , SUM(CASE WHEN col2 = 1 THEN col3 ELSE 0 END) AS Totale
from tabella
group by col1, col2)
select col1, Totale
from T
group by col1, Totale
but I'm getting
col1 | Totale
---------------
1 0
1 70
2 0
2 90
Instead of:
col1 | Totale
---------------
1 70
2 90
What's wrong with that?
My bad, I try to keep it simple but I've overdone it.. What I've to do is:
with T as (select col1 , SUM(CASE WHEN col2 = 1 THEN col3 ELSE 0 END) AS TotaleA,
SUM(CASE WHEN col2 = 2 THEN col3 ELSE 0 END) AS TotaleB,
(...)
from tabella
group by col1, col2)
select col1, TotaleA, TotaleB
from T
group by col1, TotaleA, TotaleB
And the solution is to do without col2 in the grouping... I was afraid getting "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause", but it's not. Can I mark a comment as the correct answer?