SQL Rollup实现分组表总计行：多字段求和问题咨询

嘿，我来帮你搞定这个需求！首先明确说：不需要给每个求和字段额外加CASE语句，只需要把所有要统计的字段都用SUM()函数处理，再用合适的分组方式生成总计行就行。

核心思路 #

你现在的代码只处理了Sum_Amount的求和，要实现多字段总计，只需要把TotalER、[Discount Report]也加入到分组求和的逻辑里，然后用ROLLUP（推荐）或者UNION ALL来生成总计行，只用一个CASE来控制总计行的Group Type显示。

推荐写法：用ROLLUP高效生成总计 #

ROLLUP是SQL里专门用来生成分组小计/总计的语法，比UNION ALL更简洁高效：

SELECT 
    -- 用GROUPING函数判断是否是总计行，返回1就是总计
    CASE WHEN GROUPING(GROUP_TYPE) = 1 THEN 'Total' ELSE GROUP_TYPE END AS 'Group Type',
    SUM(CAST(AMOUNT AS FLOAT)) AS Sum_Amount,
    -- 给TotalER做求和，注意如果字段是字符型要先转数值
    SUM(CAST(TotalER AS FLOAT)) AS TotalER,
    -- 同理处理Discount Report字段
    SUM(CAST([Discount Report] AS FLOAT)) AS [Discount Report]
FROM [dbo].[YourTableName] -- 替换成你的实际表名
GROUP BY ROLLUP(GROUP_TYPE)

这里GROUPING(GROUP_TYPE)会在总计行返回1，其他分组行返回0，刚好用来切换显示'Total'还是原分组值。

如果你想沿用原有的子查询+UNION ALL方式 #

也可以，但需要重复子查询逻辑，效率稍低：

SELECT 
    GROUP_TYPE AS 'Group Type',
    Sum_Amount,
    TotalER,
    [Discount Report]
FROM (
    SELECT 
        GROUP_TYPE,
        SUM(CAST(AMOUNT AS FLOAT)) AS Sum_Amount,
        SUM(CAST(TotalER AS FLOAT)) AS TotalER,
        SUM(CAST([Discount Report] AS FLOAT)) AS [Discount Report]
    FROM [dbo].[YourTableName]
    GROUP BY GROUP_TYPE
) AS GroupedData
UNION ALL
-- 单独计算所有分组的总和作为总计行
SELECT 
    'Total' AS 'Group Type',
    SUM(Sum_Amount),
    SUM(TotalER),
    SUM([Discount Report])
FROM (
    SELECT 
        SUM(CAST(AMOUNT AS FLOAT)) AS Sum_Amount,
        SUM(CAST(TotalER AS FLOAT)) AS TotalER,
        SUM(CAST([Discount Report] AS FLOAT)) AS [Discount Report]
    FROM [dbo].[YourTableName]
    GROUP BY GROUP_TYPE
) AS GroupedData

关键提醒 #

• 确保所有要SUM的字段都是数值型，如果是字符存储的数值，一定要用CAST(... AS FLOAT)或者CONVERT()转成数值再求和，避免报错。
• 只用一个CASE语句处理Group Type的显示就够了，每个求和字段直接用SUM()函数处理，不需要额外CASE。

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