Sql Subquery with sum

Question

I have two tables Invoices and Payments. invoices have payments. I want to write a query that displays unpaid invoices and the remaining amount of the invoice, which is calculated by summing up the payments of the invoice and subtracting it from the invoice amount. I tried this query but it doesn't work. please how can i do it.

SELECT Invoice.[Invoice Date], Invoice.Item, Invoice.Quantity, 
    Invoice.[Unit Price], 
    Invoice.[Payment Status], Invoice.[LongDate], 
    Invoice.Quantity*Invoice.[Unit Price] - Sum(Q.Amount) AS Remaining
FROM  
    (SELECT Invoice.[Invoice Id], [Payment ID]
       FROM Invoice 
       INNER JOIN Payment ON Invoice.[Invoice Id] = Payment.[Invoice Id]) AS Q
    INNER JOIN Invoice ON Q.[Invoice Id] = Invoice.[Invoice Id]
GROUP BY Invoice.[Invoice Id]; 

Answer 1

SELECT Invoice.[Invoice ID], 
    Sum(Invoice.Quantity * Invoice.[Unit Price]) 
    - COALESCE(Sum(Payment.Amount), 0) AS Remaining
FROM
    Invoice LEFT JOIN Payment ON Invoice.[Invoice ID] = Payment.[Invoice ID]
GROUP BY Invoice.[Invoice ID]

EDIT: I am assuming, you won't need Item related information in the result.
LEFT JOIN is used with an assumption that Invoice might not have a Payment record.

Answer 2

Try this:

SELECT
    Invoice.[Invoice Id],
    Invoice.Quantity * Invoice.[Unit Price] - COALESCE(Amount, 0) AS Remaining
FROM Invoice
LEFT JOIN (
    SELECT [Invoice Id], SUM(Amount) AS Amount
    FROM Payment
    GROUP BY [Invoice Id]
) T1
ON Invoice.[Invoice Id] = T1.[Invoice Id]

Of course you will also need to add the other columns to the select too, but I don't think they are relevant to this question so I omitted them for clarity.

Here is some test data I used to test this:

CREATE TABLE Invoice ([Invoice Id] INT NOT NULL, Quantity INT NOT NULL, [Unit Price] INT NOT NULL);
INSERT INTO Invoice ([Invoice Id], Quantity, [Unit Price]) VALUES
(1, 10, 5),
(2, 20, 10),
(3, 1, 1);

CREATE TABLE Payment ([Invoice Id] INT NOT NULL, Amount INT NOT NULL);
INSERT INTO Payment ([Invoice Id], Amount) VALUES
(1, 10),
(2, 100),
(2, 15);

And the result with this data:

Id  Remaining
1   40
2   85
3   1