SQL如何按office_name分组统计各rating等级的对应记录数

原语句存在的问题 #

• 字段/表名不匹配：原表为tbl_rating，办公字段为office_name，原语句错误使用了office、tbl_result
• 语法错误：DISTINCT office后缺少逗号分隔后续查询字段
• 逻辑错误：子查询未关联对应办公室维度，统计结果是全表的满意度总数，无法按办公室拆分

正确实现语句 #

使用条件聚合的行转列写法，兼容绝大多数SQL数据库（MySQL、PostgreSQL、SQL Server等）：

SELECT 
  office_name,
  COUNT(CASE WHEN rating = 'Satisfied' THEN 1 END) AS Satisfied,
  COUNT(CASE WHEN rating = 'Neutral' THEN 1 END) AS Neutral,
  COUNT(CASE WHEN rating = 'Unsatisfied' THEN 1 END) AS Unsatisfied
FROM tbl_rating
GROUP BY office_name
ORDER BY office_name;

语句说明 #

• 用GROUP BY office_name按办公室分组，自动得到去重后的办公室列表，不需要额外加DISTINCT
• CASE WHEN匹配对应满意度评级，匹配成功返回值，不匹配返回NULL，COUNT函数会忽略NULL值，最终统计出每个办公室对应评级的数量

如果是MySQL环境，可以用更简洁的IF函数写法：

SELECT 
  office_name,
  SUM(IF(rating='Satisfied',1,0)) AS Satisfied,
  SUM(IF(rating='Neutral',1,0)) AS Neutral,
  SUM(IF(rating='Unsatisfied',1,0)) AS Unsatisfied
FROM tbl_rating
GROUP BY office_name
ORDER BY office_name;

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