mysql 统计 一行数据多个列 中各种值的 数量

表数据

 

统计每个name 的指标中红 / 黄 / 绿 / 灰 灯数量.   分组 使用sum()函数

 

select 
name,
zhibiao1 as '指标1',
zhibiao2 as '指标2',
zhibiao3 as '指标3',
sum(case when zhibiao1 ='红' then 1 else 0 end) + sum(case when zhibiao2 ='红' then 1 else 0 end)+sum(case when zhibiao3 ='红' then 1 else 0 end)  as red_count ,
sum(case when zhibiao1 ='黄' then 1 else 0 end) + sum(case when zhibiao2 ='黄' then 1 else 0 end)+sum(case when zhibiao3 ='黄' then 1 else 0 end)  as yellow_count ,
sum(case when zhibiao1 ='绿' then 1 else 0 end) + sum(case when zhibiao2 ='绿' then 1 else 0 end)+sum(case when zhibiao3 ='绿' then 1 else 0 end)  as green_count ,
sum(case when zhibiao1 ='灰' then 1 else 0 end) + sum(case when zhibiao2 ='灰' then 1 else 0 end)+sum(case when zhibiao3 ='灰' then 1 else 0 end)  as gray_count
from info
group by name