时间戳

首页 > 解决方案 > 将显示 2 个数据总和的列连接在一起

sql - 将显示 2 个数据总和的列连接在一起

问题描述

SELECT o.ProductID ,
       p.ProductName ,
       o.unitprice AS UnitCost ,
       sum(o.Quantity) AS TotalUnitsSold ,
       (sum(o.Quantity)*o.unitprice) AS FinalCost
FROM OrderDetails o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY o.ProductID ,
         p.ProductName ,
         o.unitprice
ORDER BY 1

除了最终成本之外，我还想要一列，它给出了产品的总数，简而言之，它应该是2505.60+11772.00

sql输出

预期 o/p

ProductID    ProductName    UnitCost    TotalUnitsSold    FinalCost       Total
1                Chai            14.40        174           2505.60          14277.60
1                Chai            18.00        654           11772.00         Null

标签： sqljoingroup-byaggregate

解决方案

您可以使用窗口函数，它将为所有行提供总计：

SELECT  T.ProductID
,       T.ProductName
,       T.UnitCost
,       T.TotalUnitsSold
,       T.FinalCost
,       SUM(T.FinalCost) OVER(PARTITION BY ProductID) AS Total
FROM    (
            SELECT o.ProductID ,
                   p.ProductName ,
                   o.unitprice AS UnitCost ,
                   sum(o.Quantity) AS TotalUnitsSold ,
                   (sum(o.Quantity)*o.unitprice) AS FinalCost
            FROM OrderDetails o
            JOIN Products p ON o.ProductID = p.ProductID
            GROUP BY o.ProductID ,
                     p.ProductName ,
                     o.unitprice
        ) AS T
ORDER BY 1

推荐阅读