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 |
Perhaps this is closer to what you want? It not, see if it gives you a clue about how to "pivot".
SELECT
SUM(if(ID = 1, locationID, 0)) Location_Id_1,
SUM(if(ID = 2, locationID, 0)) Location_Id_2,
SUM(if(ID = 3, locationID, 0)) Location_Id_3,
SUM(if(ID = 4, locationID, 0)) Location_Id_4,
SUM(if(ID = 5, locationID, 0)) Location_Id_5
FROM boxlocations;
(MAX()
should give the same result as SUM()
.)
Best Answer
You may use
SUM
function withIF
to choose what to take into consideration for each row. Here is my example: