CODEWITHSUNDEEP
mysqlsql
tjurkan
tjurkan

Reputation: 667

Sum values of a column based on another boolean column, both true and false separately

I have tried to find the existing answer for this, might have missed it. So this is part of my mysql table:

+-----+-----------+------------+--------+----------+
| id  | is_actual | month      | sum    | sheet_id |
+-----+-----------+------------+--------+----------+
| 243 |         1 | 2020-02-01 |      0 |        6 |
| 244 |         0 | 2020-02-01 | 100000 |        6 |
| 245 |         1 | 2020-05-01 |      0 |        3 |
| 246 |         1 | 2020-01-01 |    110 |        3 |
| 247 |         1 | 2020-07-01 |      0 |        3 |
| 248 |         1 | 2020-03-01 |      0 |        3 |
| 249 |         1 | 2020-08-01 |      0 |        3 |
| 250 |         1 | 2020-06-01 |      0 |        3 |
| 251 |         1 | 2020-02-01 |      0 |        3 |
| 252 |         1 | 2020-04-01 |      0 |        3 |
| 253 |         0 | 2020-05-01 |      0 |        3 |
| 254 |         0 | 2020-01-01 |      0 |        3 |

I would like to sum all amounts where is_actual is true and then all amounts where is_actual is false, in the same query, something like:

SUM(CASE `core_costsbudgetpermonth`.`is_actual` = 1 THEN `core_costsbudgetpermonth`.`sum` ELSE 0 END) AS sum_actual 
SUM(CASE `core_costsbudgetpermonth`.`is_actual` = 0 THEN `core_costsbudgetpermonth`.`sum` ELSE 0 END) AS sum_not_actual

Any suggestions?

EDIT: This is my error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN core_costsbudgetpermonth.sum ELSE 0 END) FROM `core_costsbudgetpermonth' at line 1

Expected output something like:

+-------+---------+ 
+ 20833 | 130626  +
+-------+---------+

Upvotes: 0

Views: 1300

Answers (2)

Caius Jard
Caius Jard

Reputation: 74660

You don't even need the case when

SUM(is_actual * sum) as sumactual,
-SUM((is_actual-1) * sum) as sumnotactual

You can utilise the boolean being 0 or 1 - multiplying sum and the bool together gives 0 when the bool is false and sum when it's true. If you subtract 1 from the bool it becomes 0 when it's true or -1 when it's false (then all you need to do is flip that negative sum around, or (is_actual-1) * -sum - so long as there's a double negative somewhere it works out )

You can even flip the boolean with NOT:

SUM(is_actual * sum) as sumactual,
SUM((NOT is_actual) * sum) as sumnotactual

By the way, CASE WHEN has two forms:

CASE 
  WHEN booleanTest THEN outputvalue 
 [WHEN otherBooleanTest THEN otherOutputValue ...] --as many WHENs as you like
 [ELSE elseValue] 
END

CASE variableName 
  WHEN variableValue THEN outputValue 
 [WHEN variableValue THEN outputValue ...] 
 [ELSE elseValue] 
END

And also because SUM ignores NULL (which is what CASE WHEN outputs when nothing matches) you don't need the ELSE 0 either. This means your CASE WHEN form can be reduced to variations like:

SUM(CASE is_actual WHEN true THEN sum END) as sumactual,
SUM(CASE is_actual WHEN false THEN sum END) as sumnotactual

Or

SUM(CASE WHEN is_actual THEN sum END) as sumactual,
SUM(CASE WHEN NOT is_actual THEN sum END) as sumnotactual

Upvotes: 4

P.Salmon
P.Salmon

Reputation: 17655

case WHEN ... and you need a comma after the first sum

review syntax here https://dev.mysql.com/doc/refman/5.7/en/case.html

Upvotes: 1

Related Questions