带有 SUM CASE 语句的 SQL GROUP BY

Question

我知道有很多这样的问题:

SQL GROUP BY CASE statement with aggregate function

但我试过这样做:

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 

但我得到

col1  |  Totale
---------------
   1       0
   1      70
   2       0
   2      90

代替:

 col1  |  Totale
---------------
   1      70
   2      90

那有什么问题？

更新:

我的不好，我尽量保持简单，但我做得太过分了..我要做的是:

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

解决方案是在分组中没有 col2 ......我害怕“在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中”，但它不是......我可以标记一个评论为正确答案？

Answer 1

您还可以使用子查询来解决此问题:

SELECT 
       col1,
       SUM(A) TotalA,
       SUM(B) TotalB
FROM 
(
       select col1, 
              CASE WHEN col2 = 1 THEN col3 ELSE 0 END A,
              CASE WHEN col2 = 2 THEN col3 ELSE 0 END B
       from  tabella
) t
GROUP BY Col1