Grouping ID and then sum remaining values (SQL / Query)

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;

Solution

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