0

MySQL v5.7
Having this table:

id   | sprId    | load    | calc
-----|----------|---------|---------
   1 |        1 |       1 |       7
   2 |        2 |       1 |       1
   3 |        2 |       1 |       4
   4 |        2 |       0 |      11
   5 |        3 |       1 |       2
   6 |        3 |       0 |      85
   7 |        4 |       1 |       1
   8 |        4 |       1 |      13

load is BIT.
How to split calc column sum based on the load column value, and then group by sprId.

I'm trying to get results like

 sprId    | sum_calc_load0  | sum_calc_load1
----------|-----------------|-----------------
        1 |               0 |               7
        2 |              11 |               5
        3 |              85 |               2
        4 |               0 |              14

Right now I'm doing two queries, and works, but I know for sure it can be done with subqueries, something I don't know that much.

1
  • See pivot tag, GROUP BY and SUM.
    – Rick James
    Jun 2 '20 at 0:28
0
SELECT sprId, 
       COALECSE(SUM(calc*(1-load)), 0), 
       COALECSE(SUM(calc*load), 0)
FROM source_table
GROUP BY sprId
3
  • COALESCE() will return the first NOT NULL value, isn't it?
    – Kondybas
    May 28 '20 at 22:33
  • @Kondybas That's true. OP have not provided DDL, so I assume that calc or load is nullable.
    – Akina
    May 29 '20 at 5:00
  • That is, if both columns are NOT NULL, the `COALESCE is unnecessary.
    – Rick James
    Jun 2 '20 at 0:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.