如何在同一查询中统计TotalConverted列的UnderwriterId分组求和数据？

场景说明 #

你的原查询已经通过CASE语句生成了转换为USD的TotalConverted列，现在需要在同一查询中按UnderwriterId对该列求和，分两种场景实现：

场景1：保留原有明细行，同时显示对应UnderwriterId的合计值 #

使用窗口函数可以在保留所有原始数据的同时，计算每个UnderwriterId对应的TotalConverted总和，无需修改原有查询的核心逻辑：

SELECT 
    c.Id, c.Currency,
    cr.CPDealId, cr.UnderwriterId, cr.PlacedDate, cr.Fee, cr.FeeDue, cr.AllocatedFeeDue,
    f.Rate,
    -- 原有货币转换逻辑
    CASE
        WHEN c.Currency <> 'USD' THEN (cr.FeeDue / f.Rate)
        ELSE cr.FeeDue        
    END AS TotalConverted,
    -- 新增：按UnderwriterId分组求和
    SUM(
        CASE
            WHEN c.Currency <> 'USD' THEN (cr.FeeDue / f.Rate)
            ELSE cr.FeeDue        
        END
    ) OVER (PARTITION BY cr.UnderwriterId) AS UnderwriterTotal
FROM DB.DB.CPDeal c 
INNER JOIN DB.DB.CPDealRate cr 
    ON c.Id = cr.CPDealId
LEFT JOIN DB.DB.Fx f 
    ON c.Currency = f.MainCurrency
WHERE IssuerId = '1'
AND c.Date BETWEEN GETDATE()-90 AND GETDATE()
ORDER BY UnderwriterId DESC, PlacedDate DESC, Currency DESC;

说明

SUM() OVER (PARTITION BY cr.UnderwriterId)会将数据按UnderwriterId分组，对每组的TotalConverted计算总和，并且这个总和会显示在该组的每一行数据中。

场景2：仅需要按UnderwriterId分组后的求和结果 #

如果不需要保留明细，只需要每个UnderwriterId的合计值，可以将原查询封装为子查询或CTE，再进行分组求和：

方法1：使用子查询

SELECT 
    UnderwriterId,
    SUM(TotalConverted) AS UnderwriterTotal
FROM (
    -- 原查询作为子查询提取核心字段
    SELECT 
        cr.UnderwriterId,
        CASE
            WHEN c.Currency <> 'USD' THEN (cr.FeeDue / f.Rate)
            ELSE cr.FeeDue        
        END AS TotalConverted
    FROM DB.DB.CPDeal c 
    INNER JOIN DB.DB.CPDealRate cr 
        ON c.Id = cr.CPDealId
    LEFT JOIN DB.DB.Fx f 
        ON c.Currency = f.MainCurrency
    WHERE IssuerId = '1'
    AND c.Date BETWEEN GETDATE()-90 AND GETDATE()
) AS SubQuery
GROUP BY UnderwriterId
ORDER BY UnderwriterId DESC;

方法2：使用CTE（可读性更强）

WITH ConvertedData AS (
    -- 原查询逻辑封装为CTE
    SELECT 
        cr.UnderwriterId,
        CASE
            WHEN c.Currency <> 'USD' THEN (cr.FeeDue / f.Rate)
            ELSE cr.FeeDue        
        END AS TotalConverted
    FROM DB.DB.CPDeal c 
    INNER JOIN DB.DB.CPDealRate cr 
        ON c.Id = cr.CPDealId
    LEFT JOIN DB.DB.Fx f 
        ON c.Currency = f.MainCurrency
    WHERE IssuerId = '1'
    AND c.Date BETWEEN GETDATE()-90 AND GETDATE()
)
SELECT 
    UnderwriterId,
    SUM(TotalConverted) AS UnderwriterTotal
FROM ConvertedData
GROUP BY UnderwriterId
ORDER BY UnderwriterId DESC;

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