(sql)如何在一个“客户名称”下聚合“总数量”值

eimct9ow 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(236)

所以我有一个简单的问题

SELECT C.CustomerName, (P.Price * OD.Quantity) AS TotalQuantity, 
FROM Customers as C
INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails AS OD
ON O.OrderID = OD.OrderID
INNER JOIN Products AS P
ON OD.ProductID = P.ProductID
ORDER BY TotalQuantity DESC;

这就是结果

CustomerName    TotalQuantity
Piccolo und mehr    13175
Simons bistro   13175
Mère Paillarde  12911.5
Queen Cozinha   10540
Split Rail Beer & Ale   5270
Ernst Handel    5270
Blondel père et fils    4332.650000000001
Frankenversand  3850
Ernst Handel    3850
Save-a-lot Markets  3512
Rattlesnake Canyon Grocery  3451
Suprêmes délices    3240
Old World Delicatessen  3094.75
Save-a-lot Markets  2970.96
Save-a-lot Markets  2880

正如你所见，“大量储蓄市场”被分为三个记录。我的代码所做的是将每个订单的总产品数量乘以价格。我需要做的是，汇总每个客户的所有订单，因此这将是一个“大量节省市场”的记录，总数量为9362.96。
你对如何做到这一点有什么想法吗？
抱歉，解释得太草率了。

sql Aggregation

来源：https://stackoverflow.com/questions/62733869/sql-how-to-aggregate-total-quantity-values-under-one-customer-name

2条答案

按热度按时间

7bsow1i61#

听起来你想要聚合。请注意 GROUP BY 以及 SUM() :

SELECT C.CustomerName, SUM(P.Price * OD.Quantity) AS TotalQuantity, 
FROM Customers aC INNER JOIN
     Orders O
     ON C.CustomerID = O.CustomerID INNER JOIN 
     OrderDetails OD
     ON O.OrderID = OD.OrderID INNER JOIN 
     Products AS P
     ON OD.ProductID = P.ProductID
GROUP BY C.CustomerName
ORDER BY TotalQuantity DESC;

赞(0）回复(0）举报 2021-07-26

xv8emn3q2#

你必须使用聚合函数 sum 以及 group by ```
SELECT
C.CustomerName,
SUM(P.Price * OD.Quantity) AS TotalQuantity,
FROM Customers as C
INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails AS OD
ON O.OrderID = OD.OrderID
INNER JOIN Products AS P
ON OD.ProductID = P.ProductID
GROUP BY
C.CustomerName
ORDER BY
TotalQuantity DESC;

赞(0）回复(0）举报 2021-07-26

我来回答

(sql)如何在一个“客户名称”下聚合“总数量”值

2条答案

相关问题

热门标签

最新问答