SQL Server 2016中指定列求和并GROUP BY分组的SQL实现需求

没问题，我帮你把这个SQL语句补全并调整到位，完美适配SQL Server 2016的需求——对指定列求和后通过GROUP BY合并成单行。

完整SQL示例 #

SELECT 
    PRIMARY_OWNER AS 'Taxpayer Name / Doing Business As',
    BILL_NUMBER AS 'Taxpayer Id / Bill Number',
    IN_CARE_OF_NAME AS 'In Care Of',
    ADDRESS_LINE1 AS 'Address Line 1',
    ADDRESS_LINE2 AS 'Address Line 2',
    ADDRESS_LINE3 AS 'Address Line 3',
    CITY AS 'City',
    RIGHT(STATE, 2) AS 'State',
    POSTAL_CODE AS 'Zip',
    PWA.BILLING_DATE AS 'Bill Date',
    -- 补全你未写完的CASE WHEN逻辑（示例参考）
    CASE WHEN LEVY_STATUS = 'ACTIVE' THEN 'Active Levy' ELSE 'Inactive Levy' END AS 'Levy Status',
    -- 对目标列执行求和操作
    SUM(TAX_PAID) AS 'Total Tax Paid',
    SUM(INTEREST_PAID) AS 'Total Interest Paid',
    SUM(PENALTY_PAID) AS 'Total Penalty Paid',
    SUM(BALANCE_DUE) AS 'Total Balance Due'
FROM 
    -- 替换成你的实际表名（这里假设表别名PWA对应你的业务表）
    YOUR_BUSINESS_TABLE PWA
-- 可选：添加WHERE条件过滤数据，比如日期范围
WHERE 
    PWA.BILLING_DATE >= '2023-01-01'
GROUP BY 
    -- 注意：所有SELECT中未被聚合函数包裹的列/表达式，必须全部出现在GROUP BY里
    PRIMARY_OWNER,
    BILL_NUMBER,
    IN_CARE_OF_NAME,
    ADDRESS_LINE1,
    ADDRESS_LINE2,
    ADDRESS_LINE3,
    CITY,
    RIGHT(STATE, 2), -- 不能直接用别名'State'，必须和SELECT里的表达式一致
    POSTAL_CODE,
    PWA.BILLING_DATE,
    LEVY_STATUS -- 如果CASE WHEN用到了该列，必须加入GROUP BY

关键注意事项 #

• GROUP BY规则：SQL Server 2016要求所有非聚合列（也就是SELECT里没被SUM()/COUNT()等包裹的内容）必须出现在GROUP BY子句中，否则会抛出语法错误
• 求和逻辑：用SUM()函数分别对TAX_PAID、INTEREST_PAID、PENALTY_PAID、BALANCE_DUE求和，这样同一分组下的多行数据会被合并为单行，同时得到各列的累计值
• CASE WHEN处理：如果你的CASE逻辑涉及其他列，比如示例中的LEVY_STATUS，那该列必须加入GROUP BY；如果CASE是基于聚合后的结果，则需要把它放到HAVING子句或者嵌套查询中

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