Search code examples
mysqlgroup-byconditional-aggregation

MySQL split row into 2 columns and group by, is that possible?

I got a table with 2 columns app and grupo_id

select count(*) total, app, grupo_id from devices d group by app, grupo_id ;

output:

total app grupo-id
7 1 4
2 0 4
5 1 2
1 1 1
3 0 1

so the app I would like to split into 2 columns app-on and app-off and group them by grupo-id

Expected output:

app-on app-off grupo-id
7 2 4
5 0 2
1 3 1

is that possible is that possible ?

Solution

  • Use conditional aggregation:

    SELECT SUM(app = 1) app_on, 
       SUM(app = 0) app_off,
       grupo_id
FROM devices 
GROUP BY grupo_id;

    or:

    SELECT SUM(app) app_on, 
       SUM(NOT app) app_off,
       grupo_id
FROM devices 
GROUP BY grupo_id;