按照分组 ID,然后对剩余的值求和(SQL / 查询)

huangapple go评论65阅读模式
英文:

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

我看到您已经标记了SQLMS-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

huangapple
  • 本文由 发表于 2023年4月17日 18:46:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76034319.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定