Group By Question

nmccamy · February 17th, 2004, 10:25 AM

I have a table called InvoiceDetails that has the following fields:

InvoiceID
ProductID
Qty (quantity bought)
Price (product price)

I have a second table called CustPayments with the following fields:

InvoiceID
Date
Payment

What I'm trying to do in one query is to obtain the balance due for each InvoiceID. So far I have the following:

SELECT InvoiceDetails.InvoiceID, Sum([Price]*[Qty]) AS [Total Due], CustPayments.Payment
FROM InvoiceDetails INNER JOIN CustPayments ON InvoiceDetails.InvoiceID = CustPayments.InvoiceID
GROUP BY InvoiceDetails.InvoiceID, CustPayments.Payment;

How do I subtract the payments made from the total due to get the balance due in this Group By query?

This is what is I want to happen, step-by-step:

1. Sum the (Price * Qty) for each InvoiceDetails.InvoiceID
2. Sum the Payment for each CustPayments.InvoiceID
3. Subtract step 2 from step 1 for each InvoiceID to get the balance due

pgtips · February 18th, 2004, 09:14 AM

I assume you have a many-to-many link between InvoiceDetails and CustPayments? If so you won't get very far joining these 2 tables because that will result in some values being counted multiple times. For example if a certain invoice has 3 product records and 2 payment records you'll end up with 6 records in the joined table, which means your "Due" will be counted twice and your "Paid" will be counted 3 times.

Try something like this nasty-looking bit of SQL:

Code:

SELECT tmp.InvoiceID, SUM(tmp.TotalDue) AS BalanceDue
FROM [
SELECT InvoiceDetails.InvoiceID, Sum(InvoiceDetails.Qty*InvoiceDetails.Price) AS TotalDue
FROM InvoiceDetails
GROUP BY InvoiceDetails.InvoiceID
UNION
SELECT CustPayments.InvoiceID, -1*Sum(CustPayments.Payment) AS TotalDue
FROM CustPayments
GROUP BY CustPayments.InvoiceID
]. AS tmp
GROUP BY tmp.InvoiceID;

hth
Phil