如何在SQL中实现无分组整列求和并按国家分组生成新列

问题分析 #

你原代码的核心问题是：SUM(CASE...)配合GROUP BY country时，只会计算当前分组内对应列的总和，而非整个表的整列全局总和。要同时实现按国家分组、输出整列无分组求和值，需要用窗口函数或子查询来获取全局总和。

方案1：使用窗口函数（推荐，支持多数现代SQL引擎） #

这种方式直接用OVER ()子句让聚合函数忽略分组，计算整列的全局总和，同时保留按country的分组统计：

SELECT 
    country,
    -- 所有国家Headcount的全局总和（整列无分组求和）
    SUM(Headcount_UK + Headcount_Asia + Headcount_USA + Headcount_Africa) OVER () AS Total_Overall,
    -- 各国家列的全局总和
    SUM(Headcount_UK) OVER () AS Total_UK,
    SUM(Headcount_Asia) OVER () AS Total_ASIA,
    SUM(Headcount_USA) OVER () AS Total_USA,
    SUM(Headcount_Africa) OVER () AS Total_AFRICA,
    -- 当前分组内，对应国家的Headcount总和（如果需要的话）
    SUM(CASE 
            WHEN country='UK' THEN Headcount_UK
            WHEN country='ASIA' THEN Headcount_Asia
            WHEN country='USA' THEN Headcount_USA
            WHEN country='AFRICA' THEN Headcount_Africa
            ELSE 0 
        END) AS Group_Total
FROM students
GROUP BY country;

方案2：子查询预计算全局总和（兼容老版本SQL引擎） #

如果你的SQL环境不支持窗口函数，可以先用子查询算出所有全局总和，再通过交叉关联到分组结果：

WITH total_sums AS (
    SELECT 
        SUM(Headcount_UK + Headcount_Asia + Headcount_USA + Headcount_Africa) AS Total_Overall,
        SUM(Headcount_UK) AS Total_UK,
        SUM(Headcount_Asia) AS Total_ASIA,
        SUM(Headcount_USA) AS Total_USA,
        SUM(Headcount_Africa) AS Total_AFRICA
    FROM students
)
SELECT 
    s.country,
    ts.Total_Overall,
    ts.Total_UK,
    ts.Total_ASIA,
    ts.Total_USA,
    ts.Total_AFRICA,
    SUM(CASE 
            WHEN s.country='UK' THEN s.Headcount_UK
            WHEN s.country='ASIA' THEN s.Headcount_Asia
            WHEN s.country='USA' THEN s.Headcount_USA
            WHEN s.country='AFRICA' THEN s.Headcount_Africa
            ELSE 0 
        END) AS Group_Total
FROM students s
CROSS JOIN total_sums ts
GROUP BY s.country, ts.Total_Overall, ts.Total_UK, ts.Total_ASIA, ts.Total_USA, ts.Total_AFRICA;

按需调整 #

如果只需要显示全局总人数和当前分组国家对应的列总和，可以简化代码，比如只保留Total_Overall和对应国家的Total_XX列，去掉不需要的字段。

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