I have this table
type | child_id | status
-----------------------------------
1 | a | completed
1 | b | failed
1 | a | completed
1 | c | completed
-----------------------------------
2 | a | failed
2 | b | failed
I want to group the values by type, get the count of different statuses, and also get any value from the child_id, to get such a result.
type | child_id | completed | failed
-----------------------------------------
1 | b | 3 | 1
2 | a | 0 | 2
For type 1, child_id a, b or c is acceptable; and a or b for type 2.
I can use this to all the columns except child_id
SELECT type,
sum(case when status = 'completed' then 1 else 0 end) as completed,
sum(case when status = 'failed' then 1 else 0 end) as failed
from mytable
group by type;
Just need to figure out if there is a way to get the child_id in there as well, without doing a join.