# select based on sum of rows compared to another field value

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.

This is an UNPIVOT problem.To get the sum of credit items and the sum of debit items you can use a `SUM` of a `CASE` statement. To show that, we need a table first:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

``````CREATE TABLE invoice_items
(`invoice_item_id` int, `invoice_set_id` varchar(3), `credit_debit` varchar(6), `amount` int)
;

INSERT INTO invoice_items
(`invoice_item_id`, `invoice_set_id`, `credit_debit`, `amount`)
VALUES
(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)
;
``````

Now you can combine `SUM` and `CASE` like this: Query 1:

``````SELECT invoice_set_id,
SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
FROM invoice_items
GROUP BY invoice_set_id;
``````
``````| INVOICE_SET_ID | DEBIT_AMOUNT | CREDIT_AMOUNT |
|----------------|--------------|---------------|
|            a22 |           23 |            23 |
|            b23 |           44 |        (null) |
|            c55 |           17 |             8 |
``````

From here it is fairly simple to filter out the unwanted rows. We just need to wrap the two sums in `COALESCE` statements to turn `NULL` into 0:

Query 2:

``````SELECT invoice_set_id
FROM(
SELECT invoice_set_id,
SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
FROM invoice_items
GROUP BY invoice_set_id
)X
WHERE COALESCE(debit_amount,0) > COALESCE(credit_amount,0);
``````
``````| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |
``````

If your actual problem is as simple as your example and you do not need the intermediate values for anything else, you can get the result also by just changing the sign of the amount based on the `credit_debit` value before calculating the sum. That would look like this:

Query 3:

``````SELECT invoice_set_id
FROM invoice_items
GROUP BY invoice_set_id
HAVING SUM(CASE WHEN credit_debit = 'debit' THEN amount ELSE -amount END)>0;
``````
``````| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |
``````
`````` SELECT invoice_set_id FROM (SELECT invoice_set_id ,
SUM(CASE WHEN credit_debit = 'debit' THEN amount  ELSE 0 END ) as
debit_amount,
SUM(CASE WHEN credit_debit = 'credit' THEN amount ELSE 0 END ) as
credit_amount
FROM test
GROUP BY invoice_set_id  , credit_debit  ) as a
GROUP BY invoice_set_id
HAVING SUM(debit_amount) > SUM(credit_amount)  ORDER BY invoice_set_id
``````
• Can you explain A) how your answer works, and B) how your answer is different then the upvoted answer from 4 1/2 years ago? Commented Aug 13, 2018 at 11:17