Left Join with Sum

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Left Join with Sum

Post by Nunners »

I have a slightly complicated join, which involves a sum and I can't get my head around exactly how it should be built.

There are two tables:
The first a list of invoices, with the usual info - date, amount, invoiceId, details, accountId etc
The second table is a list of allocations (receiptToInvoice table) where receipts assigned to invoices (either in full or part) - amount, invoiceId, receiptId

The query I want to return is a list of invoices which have amounts outstanding for a particular accountId.

I'm working with the following:
SELECT invoices.*, (invoices.amount - SUM(receipts.total_allocated)) as total_remaining FROM invoices LEFT JOIN (SELECT receiptToInvoice.invoiceId, sum(receiptToInvoice.amount) as total_allocated FROM receiptToInvoice GROUP BY receiptToInvoice.invoiceId) AS receipts ON receipts.invoiceId = invoices.invoiceId WHERE accountId='1' AND (invoices.amount - SUM(receipts.total_allocated))>0 ORDER BY invoiceDate ASC
The error I get is a group use error - but I know the LEFT JOINED select is correct, with the GROUP, but I'm not sure exactly how it should fit in with the rest of the query.

Any help gratefully received!!!!

Cheers
Nunners
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Post by Nunners »

Thinking about this a bit more, not sure I need the sub-query in the JOIN, but even using:
SELECT invoices.*, SUM(receiptToInvoice.amount) as total_allocated
FROM invoices
LEFT JOIN receiptToInvoice ON receiptToInvoice.invoiceId=invoices.invoiceId
WHERE accountId='1' AND invoices.amount-SUM(receiptToInvoice.amount)>0 GROUP BY receiptToInvoice.invoiceId ORDER BY invoiceDate ASC

But again, it reports back a group error....
Post Reply