如何使用CASE语句结合COUNT DISTINCT统计唯一ID的汇总数据

你编写的SQL中添加了GROUP BY [business_unit]子句，同时查询字段包含了[business_unit]，会导致统计逻辑变为按每个独立的业务单位分别计算去重计数，无法得到全局汇总结果。

删除GROUP BY子句和查询列表中的[business_unit]字段，即可得到一行全局汇总的统计结果：

SELECT 
    COUNT(DISTINCT [business_unit]) AS 'COUNT',
    COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '11%') THEN [business_unit] END) AS '11',
    COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '49%') THEN [business_unit] END) AS '49',
    COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '41%') THEN [business_unit] END) AS '41',
    COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '40%') THEN [business_unit] END) AS '40'
FROM 
    [Table]

如果你需要直接输出你要求的拼接格式结果，可以使用数据库自带的字符串拼接、格式化函数实现，以SQL Server为例：

SELECT CONCAT(
    'Count ', FORMAT(COUNT(DISTINCT [business_unit]), 'N0'),
    ' | 11 = ', COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '11%') THEN [business_unit] END),
    ' | 40 = ', COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '40%') THEN [business_unit] END),
    ' | 41 = ', COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '41%') THEN [business_unit] END),
    ' | 49 = ', COUNT(DISTINCT CASE WHEN ([business_unit] LIKE '49%') THEN [business_unit] END)
) AS 汇总结果
FROM [Table]

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