如何用SQL按cus_id分组统计不同prod_id频次及income总和

你的初始查询按cus_id和prod_id分组，得到的是每个用户对应单个产品的拆分统计结果，但要实现将不同prod_id的计数转为列展示的需求，需要使用条件聚合实现行转列效果，同时仅按cus_id分组即可。

优化后的SQL语句 #

SELECT
  cus_id,
  SUM(CASE WHEN prod_id = 10 THEN 1 ELSE 0 END) AS `count(prod_id = 10)`,
  SUM(CASE WHEN prod_id = 20 THEN 1 ELSE 0 END) AS `count(prod_id = 20)`,
  SUM(CASE WHEN prod_id = 30 THEN 1 ELSE 0 END) AS `count(prod_id = 30)`,
  SUM(income) AS `sum(income)`
FROM t
GROUP BY cus_id;

语句说明 #

• 条件聚合逻辑：通过CASE WHEN判断每条记录的prod_id是否匹配目标值，匹配返回1、不匹配返回0，再用SUM累加得到对应prod_id的出现次数（也可替换为COUNT(CASE WHEN prod_id=10 THEN 1 END)，COUNT会自动忽略NULL值）。
• 分组调整：仅按cus_id分组，确保每个用户只生成一行汇总结果。
• 收入总和计算：直接用SUM(income)即可得到每个用户的收入总和，无需额外分组维度。

执行结果验证 #

运行上述语句后，会输出你期望的格式：

cus_id     count(prod_id = 10)    count(prod_id = 20)    count(prod_id = 30)     sum(income)
100                2                     1                 0                280
122                0                     1                 1                 19

内容的提问来源于stack exchange，提问作者hjun