Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    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. #2
    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;
    I won't byte... hard!

  3. #3
    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. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    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. #5
    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. #6
    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. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    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. #8
    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. #9
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    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. #10
    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. #11
    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. #12
    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. #13
    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. #14
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    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. #15
    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
  •