PSQL - Group by with sum and a random value from the group

Viewed 26

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.

0 Answers
Related