# Thread: Group By Question

1. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22

## Unanswered: Group By Question

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

2. Registered User
Join Date
Feb 2004
Location
Binary Universe
Posts
57
Try this:

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

3. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by The Byte
Try this:

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

I get a FROM clause error using the above.

4. Registered User
Join Date
Dec 2003
Location
Posts
238
Originally posted by nmccamy
I get a FROM clause error using the above.
I think it's missing the space between the "as [Balance Due]" and the "FROM"... SQL is picky about spacing...

HTH

5. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by Trudi
I think it's missing the space between the "as [Balance Due]" and the "FROM"... SQL is picky about spacing...

HTH
Here is what I have:

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

Still gives a FROM clause error.

6. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by nmccamy
Here is what I have:

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

Still gives a FROM clause error.
An inner join syntax is as follows:

SELECT fields
FROM table1
INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];

7. Registered User
Join Date
Dec 2003
Location
Posts
238
Originally posted by nmccamy
An inner join syntax is as follows:

SELECT fields
FROM table1
INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];
Try this...

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

8. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by Trudi
Try this...

SELECT InvoiceDetails.InvoiceID, Sum([Price]*[Qty]) AS [Total Due], Payment, Sum([Price]*[Qty]) - Sum(Payment) AS [Balance Due] FROM InvoiceDetails INNER JOIN CustPayments ON InvoiceDetails.InvoiceID = CustPayments.InvoiceID GROUP BY InvoiceDetails.InvoiceID, CustPayments.Payment;
That works only if you do not have multiple products or multiple payments for each InvoiceID.

I can e-mail you full details of a simple database and what I'm trying to do, then you can try out your theories on that if you would like?

9. Registered User
Join Date
Dec 2003
Location
Posts
238
Originally posted by nmccamy
That works only if you do not have multiple products or multiple payments for each InvoiceID.

I can e-mail you full details of a simple database and what I'm trying to do, then you can try out your theories on that if you would like?
I can't receive attachments here at work, but I'd love to see it... Could you post a scaled down version of the database so that I can take a peek?

10. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by Trudi
I can't receive attachments here at work, but I'd love to see it... Could you post a scaled down version of the database so that I can take a peek?
OK. I'll put together a very simple example and post it. Give me a few minutes.

11. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by nmccamy
OK. I'll put together a very simple example and post it. Give me a few minutes.
Here is the Invoice table:

InvoiceID ProductID Qty Price
1 4 1 \$20.00
1 1 3 \$10.00
1 6 1 \$50.00
2 2 1 \$60.00

Customer1 (InvoiceID = 1) has purchased six items:

1 @ \$20
3 @ \$10
1 @ \$50

for a total due of \$100.

Customer2 (InvoiceID = 2) purchased 1 item for \$60.

Here is the CustPayment table:

InvoiceID Date Payment
1 1/4/2004 \$30.00
1 1/10/2004 \$70.00
2 2/10/2004 \$20.00
2 2/20/2004 \$5.00

Customer1 made two payments totaling \$100, so their balance due is \$0.
Customer2 made two payments totaling \$25, so their balance due is \$60 - 25 = \$35.

How do I get the balance due fro each InvoiceID in a single query?
Perhaps there's a better way to create the tables?

12. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by nmccamy
Here is the Invoice table:

InvoiceID ProductID Qty Price
1 4 1 \$20.00
1 1 3 \$10.00
1 6 1 \$50.00
2 2 1 \$60.00

Customer1 (InvoiceID = 1) has purchased six items:

1 @ \$20
3 @ \$10
1 @ \$50

for a total due of \$100.

Customer2 (InvoiceID = 2) purchased 1 item for \$60.

Here is the CustPayment table:

InvoiceID Date Payment
1 1/4/2004 \$30.00
1 1/10/2004 \$70.00
2 2/10/2004 \$20.00
2 2/20/2004 \$5.00

Customer1 made two payments totaling \$100, so their balance due is \$0.
Customer2 made two payments totaling \$25, so their balance due is \$60 - 25 = \$35.

How do I get the balance due fro each InvoiceID in a single query?
Perhaps there's a better way to create the tables?

I'm formatting it better here:

Here is the Invoice table:

InvoiceID ProductID Qty Price
1 4 1 \$20.00
1 1 3 \$10.00
1 6 1 \$50.00
2 2 1 \$60.00

Customer1 (InvoiceID = 1) has purchased six items:

1 @ \$20
3 @ \$10
1 @ \$50

for a total due of \$100.

Customer2 (InvoiceID = 2) purchased 1 item for \$60.

Here is the CustPayment table:

InvoiceID Date Payment
1 1/4/2004 \$30.00
1 1/10/2004 \$70.00
2 2/10/2004 \$20.00
2 2/20/2004 \$5.00

Customer1 made two payments totaling \$100, so their balance due is \$0.
Customer2 made two payments totaling \$25, so their balance due is \$60 - 25 = \$35.

How do I get the balance due for each InvoiceID in a single query?
Perhaps there's a better way to create the tables?

13. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by nmccamy
I'm formatting it better here:

Here is the Invoice table:

InvoiceID ProductID Qty Price
1 4 1 \$20.00
1 1 3 \$10.00
1 6 1 \$50.00
2 2 1 \$60.00

Customer1 (InvoiceID = 1) has purchased six items:

1 @ \$20
3 @ \$10
1 @ \$50

for a total due of \$100.

Customer2 (InvoiceID = 2) purchased 1 item for \$60.

Here is the CustPayment table:

InvoiceID Date Payment
1 1/4/2004 \$30.00
1 1/10/2004 \$70.00
2 2/10/2004 \$20.00
2 2/20/2004 \$5.00

Customer1 made two payments totaling \$100, so their balance due is \$0.
Customer2 made two payments totaling \$25, so their balance due is \$60 - 25 = \$35.

How do I get the balance due for each InvoiceID in a single query?
Perhaps there's a better way to create the tables?
Looks like my spaces are removed. Oh well.

14. Registered User
Join Date
Dec 2003
Location
Posts
238
Originally posted by nmccamy
Looks like my spaces are removed. Oh well.
Okay... That all makes sense... and I'm working on the SQL for it... Is it possible for you to post the actual database (scaled down version with no confidential information)?... I know I said that I can't receive attachments by email, but I can (so far anyway) download from here...

15. Registered User
Join Date
Feb 2004
Location
Lakeland, Florida
Posts
22
Originally posted by Trudi
Okay... That all makes sense... and I'm working on the SQL for it... Is it possible for you to post the actual database (scaled down version with no confidential information)?... I know I said that I can't receive attachments by email, but I can (so far anyway) download from here...

How do I attach a database file in this forum?
Looks like the mdb extension is not allowed as an attachment.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•