mysql self join returning blank values

Question

I have the following mysql query

select a.agency_name, a.green ,b.red, c.amber
from (select count(action_status) as green, agency_name from tbl_actions
where action_status='In Progress' group by agency_name) a
join (select count(action_status) as red, agency_name from tbl_actions
where action_status='Delayed' group by agency_name) b on a.agency_name=b.agency_name
join (select count(action_status) as amber, agency_name from tbl_actions
where act_status='Completed' group by agency_name) c on a.agency_name=c.agency_name

and it is returning blank values as

I do have these fields in the db populated with relevant data.

This query will work only if there is atleast one row for each type of `act_status` per `agency_name` — Gurwinder Singh, Dec 31 '16 at 14:53

score 2 · Accepted Answer · answered Dec 31 '16 at 14:55

Conditional aggregation is much simpler:

select a.agency_name,
       sum(a.action_status = 'In Progress') as green,
       sum(a.action_status = 'Delayed') as red,
       sum(a.action_status = 'completed') as amber
from tbl_actions a
group by a.agency_name;

A self-join is not necessary. The reason you are getting no rows in the result set is because no agency has all three statuses.

mysql self join returning blank values

1 Answers1