如何按SalesOrderID汇总重复行的Weight值？

我需要获取每个订单的总重量，对查询结果中的Weight字段按订单求和。当前查询返回的明细数据如下：

SalesOrderID SalesOrderDetailID SubTotal              CompanyName                                                                                                                      Weight
------------ ------------------ --------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
71774        110562             880,3484              Good Toys                                                                                                                        1061.40
71774        110563             880,3484              Good Toys                                                                                                                        988.83
71776        110617             78,81                 West Side Mart                                                                                                                   317.00
71780        110616             38418,6895            Nearby Cycle Shop                                                                                                                5098.36
71780        110617             38418,6895            Nearby Cycle Shop                                                                                                                24874.88
71780        110618             38418,6895            Nearby Cycle Shop                                                                                                                78053.76
71780        110619             38418,6895            Nearby Cycle Shop                                                                                                                2431.24
71780        110620             38418,6895            Nearby Cycle Shop                                                                                                                12596.19

当前使用的查询语句：

SELECT a.SalesOrderID, c.SalesOrderDetailID, a.SubTotal,b.CompanyName,
(SELECT c.OrderQty*d.Weight WHERE c.SalesOrderID=c.SalesOrderID) AS Weight
FROM SalesLT.SalesOrderHeader as a
INNER JOIN SalesLT.Customer AS b
ON a.CustomerID=b.CustomerID
INNER JOIN SalesLT.SalesOrderDetail AS c
ON c.SalesOrderID=a.SalesOrderID
INNER JOIN SalesLT.Product as d
ON d.ProductID=c.ProductID

我尝试用SUM(CASE WHEN)求和时报错，想知道其他可行方法。期望输出示例：71774 | 880,3484 | Good Toys | 2050,23（2050,23为该订单两条Weight记录的总和）。

方法1：使用GROUP BY分组求和 #

这是最直接的方式，先修正单条明细的重量计算（原查询的子查询写法冗余且有误），再按订单维度分组汇总：

SELECT 
    a.SalesOrderID, 
    a.SubTotal, 
    b.CompanyName, 
    -- 按订单汇总所有明细的重量（订单数量×产品重量）
    SUM(c.OrderQty * d.Weight) AS TotalWeight
FROM SalesLT.SalesOrderHeader as a
INNER JOIN SalesLT.Customer AS b
    ON a.CustomerID = b.CustomerID
INNER JOIN SalesLT.SalesOrderDetail AS c
    ON c.SalesOrderID = a.SalesOrderID
INNER JOIN SalesLT.Product as d
    ON d.ProductID = c.ProductID
-- 按订单唯一标识分组，确保同一订单的所有明细被汇总
GROUP BY a.SalesOrderID, a.SubTotal, b.CompanyName
-- 可选：按订单ID排序，方便查看
ORDER BY a.SalesOrderID;

方法2：使用窗口函数（如需同时保留明细和汇总） #

如果需要在保留明细行的同时显示对应订单的总重量，可以用窗口函数SUM() OVER (PARTITION BY ...)：

SELECT 
    a.SalesOrderID, 
    c.SalesOrderDetailID,
    a.SubTotal, 
    b.CompanyName, 
    c.OrderQty * d.Weight AS DetailWeight,
    -- 计算当前订单的总重量
    SUM(c.OrderQty * d.Weight) OVER (PARTITION BY a.SalesOrderID) AS TotalWeight
FROM SalesLT.SalesOrderHeader as a
INNER JOIN SalesLT.Customer AS b
    ON a.CustomerID = b.CustomerID
INNER JOIN SalesLT.SalesOrderDetail AS c
    ON c.SalesOrderID = a.SalesOrderID
INNER JOIN SalesLT.Product as d
    ON d.ProductID = c.ProductID;

关于SUM(CASE WHEN)报错的说明 #

你之前用SUM(CASE WHEN)报错，大概率是因为没有配合GROUP BY分组，或者CASE WHEN的逻辑不符合分组需求。比如如果没有分组，SUM()会计算全局总和，而不是按订单分组；如果CASE WHEN的条件没有正确关联订单ID，也会导致结果错误或语法报错。

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