select based on sum of rows compared to another field value

Question

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.

Answer 1

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;

Results:

| 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);

Results:

| 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;

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |

Answer 2

 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