I have a table of invoice items. A single transaction may result in multiple debit items and multiple credit items sharing the same invoice_set_id, I need to compare the sum of the debit items against the sum of the credit items and add the invoice_set_id to the result set if sum(debits) > sum(credits). It should also add to the result if there is no row with a credit amount. Using mySQL. Thanks for any help. Example table and result follows:
invoice_item_id invoice_set_id credit_debit amount 62 a22 debit 15.00 63 a22 debit 8.00 64 a22 credit 23.00 65 b23 debit 44.00 66 c55 debit 15.00 67 c55 debit 2.00 67 c55 credit 8.00
Given the above, the result set should be:
invoice_set_id b23 c55
Explanation: a22 is not returned because the debits and credits are equal, b23 is returned because it has a debit but no credit, and c55 is returned because the sum of the debits is greater than the single credit.
I appreciate any help with this. The actual query is more involved, but I think this particular problem is all I need help with.