GROUP BY with a SUM CASE expression
10:11 13 Aug 2014

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?

sql sql-server