Sql Subquery with sum

[ashukite]

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];

[Delerna]

There are a number of things wrong in the "basics" of your query.
You will learn more if you can fiure this one out for yourself. It really is a simple thing to do. I will try and assist you to discover a working query.
Firstly,

It doesnt work

doesn't work for me either.
Try and be more specific than that.....WHY doesn't it work?
What is it or isn't it doing? That helps us because we don't have to scrutinise your query so much just to find out what you caould have told us.

Anyway.
You are selecting from a subquery joined to a table...Why?
You shouldn't need anything like that. You are just joining the same data twice
Once in the subquery and a second time outside of the sub query.

You should be able to get you resuts from this framework
[code=sql]
SELECT YouRequiredFiel ds
FROM Invoice
LEFT JOIN Payment
[/code]
Notice I used LEFT JOIN
This is because you can have an Invoice without a Payment
INNER JOIN would remove all Invoices that don't have a pament
LEFT JOIN will keep all invoices regardless of whether they have a payment

Try writing your query like that (without any summing or grouping) first
Make sure you are getting all records. Then you can try grouping and summing the records. Always write any queries using tecniques that you are unsure of in a progressive manner like that.

The second thing wrong with your posted query is the way you are aggregating it.
Every selected field in an aggregate query must have some form of aggregation on it.
You are grouping by InvoiceID
You are summing by Q.Amount and Q.Amount doesn't even exist in Q
But what about all the other fields? You are not aggregating any of them in any way.

ALL fields selected in an aggregate query MUST be aggregated in some way.
They ALL should be in one of the aggregate functions. GROUP, SUM, MIN, MAX, etc

[ashukite]

Sorry for not being specific, 'm not experienced in SQL. Thanks for your answer, i'll try it that way

[Delerna]

Sorry for not being specific, 'm not experienced in SQL. Thanks for your answer, i'll try it that way

That's fine, thats why you came here and asked the question.
That's why I come here, to give a helping hand to people like yourself.
Just like i received when I was not very experienced.

Let us know how you get on....either way, success or fail.

[ashukite]

I used this and it works,

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

thanks