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

时间:2023-12-30 21:01:02
select whbmbh ,zt,1 as tjsl from fyxx group by zt,whbmbh

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

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后按条件分列统计)

下面是摘自别人的博客

最近遇到一个问题,需要对一张表做统计,这个统计有什么特别之处值得我记录了下来呢?大家知道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