【数据库】SQL分组多列统计(GROUP BY后按条件分列统计)

yanglang 2021-08-07 原文

select whbmbh ,zt,1 as tjsl from fyxx group by zt,whbmbh

select   whbmbh,sum(case zt when \'有效\' then 1 end) as yxsl,sum(case zt when \'暂缓\' then 1 end )as zhsl,sum(case zt when \'未知\' then 1 end) as wzsl,sum(case zt when \'我租\' then 1 end) as wzsl,sum(case zt when \'他租\' then 1 end) as tzsl,sum(case zt when \'我售\' then 1 end) as wssl,sum(case zt when \'他售\' then 1 end) as tssl ,sum(case zt when \'撤单\' then 1 end) as cdsl,sum(case zt when \'电话错误\' then 1 end) as dhcwsl,sum(case zt when \'待确认\' then 1 end) as dqrsl,sum(case zt when \'已驳回\' then 1 end) as ybhsl from fyxx  group by whbmbh

下面是摘自别人的博客

最近遇到一个问题，需要对一张表做统计，这个统计有什么特别之处值得我记录了下来呢？大家知道SQL中聚合函数GROUP BY的结果一般为一列，即多个值通过聚合函数运算统计到一起，但是如何将不同条件的值统计到不同列中呢，即按条件统计到多个列中。举个栗子：

YEAR	TYPE	VALUE
2015	1	100
2015	2	200
2016	1	150
2016	2	300
2016	3	100

转为：

YEAR	TYPE1	TYPE2	TYPE3
2015	100	200	0
2016	150	300	100

这时候我们除了用到GROUP BY之外还需要CASE WHEN，SQL如下：

SELECT year, SUM(CASE WHEN type=1 THEN value ELSE 0 END) as type1, SUM(CASE WHEN type=2 THEN value ELSE 0 END) as type2, SUM(CASE WHEN type=3 THEN value ELSE 0 END) as type3, FROM table_test GROUP BY year