英文:
Grouping ID and then sum remaining values (SQL / Query)
问题
我明白你的问题。你希望显示每个客户的购买总额、欠款和已付款的表格,但在查询时出现了重复的问题。下面是修改后的查询代码,可以得到你期望的结果:
SELECT Clients.ClientID,
Sum(Payments.PaymentAmount) AS SumOfPaymentAmount,
Sum([NewPrice]*[Qty]) AS TotalItemsPrice
FROM Clients
INNER JOIN Payments ON Clients.ClientID = Payments.ClientID
INNER JOIN (SELECT OrderID, ClientID, Sum([NewPrice]*[Qty]) AS TotalItemsPrice
FROM OrdersInventory
GROUP BY OrderID, ClientID) AS SubQuery
ON Clients.ClientID = SubQuery.ClientID
GROUP BY Clients.ClientID;
这个查询首先连接了Clients表和Payments表,然后通过子查询连接了OrdersInventory表,以获取每个订单的TotalItemsPrice。最后,使用GROUP BY将结果按ClientID分组,以得到每个客户的总付款金额和购买总额。
英文:
I am creating a database where clients can buy materials but they are free to pay whenever they want (weekly, for instance). I would like to know if there is a way to display a table with every client (no duplicates) showing how much he/she bought, how much he/she owes and how much he/she has paid.
I have three tables:
- Clients (client info)
- Payments (every time someone pays it gets
registered) - OrdersInventory (what was purchased and each item price)
Clients | Payments | OrdersInventory |
-----------| --------------|-----------------|
| PaymentsID | ItemID |
ClientsID >|>ClientID | Qty |
| PaymentAmount | NewPrice |
| OrderID >|>OrderID |
The problem occurs when I try to select the data that I need. In this case, there are 2 payments made by one person (PaymentID 3 = $10, and ID 4 = $30) but it is displaying two times the first payment because it is reading two OrderIDs from OrderInventory (in other words, the order contains two items) and NewPrice*Qty
is the total price for each item (Header name: TotalItemsPrice).
ClientID | PaymentID | PaymentAmount | TotalItemsPrice
1 | 3 | $10 | 10
1 | 3 | $10 | 15
1 | 4 | $30 | 30
If I sum TotalItemsPrice, I get a good result:
ClientID | PaymentID | PaymentAmount | TotalItemsPrice
1 | 3 | $10 | 25
1 | 4 | $30 | 30
Then I got rid of the PaymentID so that later it shows one time ClientID (no duplicates).
ClientID | PaymentAmount | TotalItemsPrice
1 | $10 | 25
1 | $30 | 30
Finally, I summed PaymentAmount with the hope of showing only those two values summed ($10+$30 = $40), but instead it get $50 because of the same issue that it is showing it two times the payment when accessing data from OrderInventory.
Result:
ClientID | SumOfPaymentAmount | TotalItemsPrice
1 | $50 | $55
Expected result:
ClientID | SumOfPaymentAmount | TotalItemsPrice
1 | $40 | $55
I can't manage to do it properly. Could anyone maybe write a solution? I appreciate all help!
This is the final code:
SELECT Clients.ClientID, Sum(Payments.PaymentAmount) AS SumOfPaymentAmount, Sum([NewPrice]*[Qty]) AS TotalItemsPrice
FROM Clients RIGHT JOIN (Payments INNER JOIN OrdersInventory ON Payments.OrderID = OrdersInventory.OrderID) ON Clients.ClientID = Payments.ClientID
GROUP BY Clients.ClientID;
答案1
得分: 0
我看到您已经标记了SQL
和MS-ACCESS
,根据您使用的数据库管理系统(DBMS)不同,一个解决方案是在JOIN
之前使用OUTER APPLY
来汇总您的OrdersInventory
表。
SELECT c.ClientID
,SUM(p.PaymentAmount) AS SumOfPaymentAmount
,SUM(oi.TotalItemsPrice) AS TotalItemsPrice
FROM Clients c
LEFT JOIN Payments p ON p.ClientID = c.ClientID
OUTER APPLY (SELECT SUM(Qty * NewPrice) AS TotalItemsPrice
FROM OrdersInventory
WHERE OrderID = p.OrderID) oi
GROUP BY c.ClientID
如果没有OUTER APPLY
可用,您也可以使用LEFT JOIN
来实现基本相同的功能。
SELECT c.ClientID
,SUM(p.PaymentAmount) AS SumOfPaymentAmount
,SUM(oi.TotalItemsPrice) AS TotalItemsPrice
FROM Clients c
LEFT JOIN Payments p ON p.ClientID = c.ClientID
LEFT JOIN (SELECT OrderID
,SUM(Qty * NewPrice) AS TotalItemsPrice
FROM OrdersInventory
GROUP BY OrderID) oi ON oi.OrderID = p.OrderID
GROUP BY c.ClientID
英文:
I see you've tagged both SQL
and MS-ACCESS
, depending on what DBMS
you're using, one solution would be to use OUTER APPLY
to aggregate your OrdersInventory
table before the JOIN
.
SELECT c.ClientID
,SUM(p.PaymentAmount) AS SumOfPaymentAmount
,SUM(oi.TotalItemsPrice) AS TotalItemsPrice
FROM Clients c
LEFT JOIN Payments p ON p.ClientID = c.ClientID
OUTER APPLY (SELECT SUM(Qty * NewPrice) AS TotalItemsPrice
FROM OrdersInventory
WHERE OrderID = p.OrderID) oi
GROUP BY c.ClientID
If OUTER APPLY
isn't available, you can do essentially the same thing with LEFT JOIN
.
SELECT c.ClientID
,SUM(p.PaymentAmount) AS SumOfPaymentAmount
,SUM(oi.TotalItemsPrice) AS TotalItemsPrice
FROM Clients c
LEFT JOIN Payments p ON p.ClientID = c.ClientID
LEFT JOIN (SELECT OrderID
,SUM(Qty * NewPrice) AS TotalItemsPrice
FROM OrdersInventory
GROUP BY OrderID) oi ON oi.OrderID = p.OrderID
GROUP BY c.ClientID
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论