sql计算分组

Question

有没有人知道如何用case语句将两个类别的和组合在一起？

在下面的屏幕截图中，我想将两行合并为一行，只在flag1total和flag2total列中显示PE和NPE总和，但它不起作用。

这是我目前拥有的SQL：

select type, flag, coalesce(sum(value),0) total,
case when flag = "NPE" then coalesce(sum(value),0) else "" end as flag1total,
case when flag = "PE" then coalesce(sum(value),0) else "" end as flag2total
from maintable
group by type, flag;

​

​

数据如下所示

 type     flag          value
 P_OTH    PE            23525
 P_OTH    PE            13525
 P_CRE    PE            232525
 P_OTH    PE            4525
 ....

预期的结果是这样的(每种类型一行，标志的矢量化和)：

 type     flag          total      flag1total   flag2total
 P_OTH    PE, NPE       3023624    1132707      1890917

Answer 1

你需要在你的sum函数中使用case。尝试如下所示：

select type, coalesce(sum(value),0) total,
    coalesce(sum(case when flag = "NPE" then value else 0 end),0)   as flag1total,
    coalesce(sum(case when flag = "PE" then value else 0 end),0)  as flag2total
 from maintable
 group by type;

请注意，“flag”不能出现在group by子句中。

Answer 2

你需要下面的-

SELECT  type
       ,GROUP_CONCAT(flag, ', ') flag
       ,coalesce(sum(value),0) total
       ,coalesce(sum(case when flag = "NPE" then value else 0 end),0)   as flag1total
       ,coalesce(sum(case when flag = "PE" then value else 0 end),0)  as flag2total
 from maintable
 group by type;