SQL Server 如何获得重复行的总和？

rbl8hiat 于 2022-12-03 发布在其他

关注(0)|答案(3)|浏览(146)

我需要得到订单的权重，所以我需要对结果求和。这个表看起来像这样

SalesOrderID SalesOrderDetailID SubTotal              CompanyName                                                                                                                      Weight
------------ ------------------ --------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
71774        110562             880,3484              Good Toys                                                                                                                        1061.40
71774        110563             880,3484              Good Toys                                                                                                                        988.83
71776        110567             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设为sum（case when），但这会导致错误。还有其他方法吗？预期输出：71774| 880，3484个|好玩具|2050年，23岁
2050，23是两行权重之和

sql-server

来源：https://stackoverflow.com/questions/74611203/how-to-get-sum-of-repeated-rows

3条答案

按热度按时间

siv3szwd1#

您可以使用

WITH TMP_TABLE AS
(
    SELECT
        a.SalesOrderID,
        c.SalesOrderDetailID,
        a.SubTotal,
        b.CompanyName,
        (c.OrderQty * d.Weight) 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
)
SELECT SalesOrderId,
    SubTotal,
    CompanyName,
    SUM(Weight)
FROM TMP_TABLE
GROUP BY SalesOrderId,
    SubTotal,
    CompanyName

赞(0）回复(0）举报 2022-12-03

hgb9j2n62#

SELECT SalesOrderId,SUM(Weight) SumOfOrderWeights
FROM SalesLT.SalesOrderDetail
GROUP BY SalesOrderId
ORDER BY SalesOrderId

赞(0）回复(0）举报 2022-12-03

oxalkeyp3#

您的数据

declare @a table(
   SalesOrderID       INTEGER  NOT NULL 
  ,SalesOrderDetailID INTEGER  NOT NULL
  ,SubTotal            VARCHAR(60)  NOT NULL
  ,CompanyName        VARCHAR(60) NOT NULL
  ,Weight            float NOT NULL
 
);
INSERT INTO @a
(SalesOrderID,SalesOrderDetailID,SubTotal,CompanyName,Weight) VALUES 
(71774,110562,'880,3484','Good Toys',1061.40),
(71774,110563,'880,3484','Good Toys',988.83),
(71776,110567,'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 SalesOrderID,SubTotal,CompanyName,sum(Weight) Weight from @a
where CompanyName='Good Toys'  --removing filter
group by SalesOrderID,SubTotal,CompanyName

赞(0）回复(0）举报 2022-12-03

我来回答

SQL Server 如何获得重复行的总和？

3条答案

相关问题

热门标签

最新问答