I have one table and trying to subtract the total where a condition is True from the full total.
| Ticket | Amount | Code |
|---|---|---|
| 11 | 5.00 | |
| 12 | 3.00 | X |
| 13 | 10.00 | |
| 14 | 2.00 | X |
My query was
SELECT SUM(AMOUNT)
FROM Table
MINUS
SELECT SUM(Amount)
FROM Table
WHERE Code = 'X'
So the answer should be 20 - 5= 15
CodePudding user response：
Below two possible queries:
-- Use IF operator
SELECT SUM(amount) - SUM(IF(code = 'X', amount, 0)) FROM tbl;
-- Use implicit MySQL conversion boolean to int (true => 1)
SELECT SUM(amount) - SUM(amount * (code = 'X')) FROM tbl;