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

user_4209009 0
Viewed 35

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 ?

conditional-aggregation group-by mysql
2 Answers

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;
user_104988280

I think you are looking for conditional aggregation

based on your first result

select grupo_id,
         max(case when app = 1 then total else 0 end) appopen,
         max(case when app = 0 then total else 0 end) appclosed
from t
group by grupo_id;

+----------+---------+-----------+
| grupo_id | appopen | appclosed |
+----------+---------+-----------+
|        1 |       1 |         3 |
|        2 |       5 |         0 |
|        4 |       7 |         2 |
+----------+---------+-----------+
3 rows in set (0.00 sec)

It may be possible to use this principal on your original query..

user_61524000
Related