使用SQL Case表达式统计各年龄段数量及占比的实现问题

问题原因 #

你的原有查询存在两个核心错误：

• 将CASE分类逻辑嵌套在COUNT()聚合函数内部，只会统计所有满足CASE条件的总行数，无法拆分出各年龄组的独立统计值
• 未添加GROUP BY分组逻辑，查询结果只会返回全局总计数，不会按年龄组拆分输出

正确实现代码 #

SELECT
    AgeGroup,
    COUNT(*) AS [Count],
    -- 计算占比并格式化输出为带百分号的格式，可根据需求调整小数位数
    FORMAT(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), '0.#%') AS [Percentage]
FROM (
    -- 子查询先为每条数据标记所属年龄组
    SELECT
        CASE
            WHEN birth_year BETWEEN 1928 AND 1945 THEN 'Silent'
            WHEN birth_year BETWEEN 1946 AND 1964 THEN 'Boomers'
            WHEN birth_year BETWEEN 1965 AND 1980 THEN 'Generation X'
            WHEN birth_year BETWEEN 1981 AND 1996 THEN 'Millennial'
            WHEN birth_year BETWEEN 1997 AND 2012 THEN 'Generation Z'
            ELSE 'Other'
        END AS AgeGroup
    FROM CombinedTable
) AS AgeGroupTable
-- 按年龄组聚合统计
GROUP BY AgeGroup
-- 可选：按代际时间顺序排序，避免按字符串首字母排序导致顺序混乱
ORDER BY 
    CASE AgeGroup
        WHEN 'Silent' THEN 1
        WHEN 'Boomers' THEN 2
        WHEN 'Generation X' THEN 3
        WHEN 'Millennial' THEN 4
        WHEN 'Generation Z' THEN 5
        ELSE 6
    END

可选调整说明 #

• 不需要展示Other分组：在子查询的FROM语句后添加WHERE birth_year BETWEEN 1928 AND 2012，即可过滤超出年龄范围的记录
• 需要数值型百分比而非格式化字符串：将Percentage字段的计算逻辑替换为COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS Percentage，直接返回浮点型百分比数值
• 调整百分比小数位数：修改FORMAT函数的格式参数即可，比如'0.0%'保留1位小数，'0%'保留整数

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