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:

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

Expected output:

is that possible is that possible ?

CodePudding user response：

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;

CodePudding user response：

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..