SQL select total and split into success and failed

Question

I have two tables

Table 1                Table 2                    |leadid|Location|      |leadid|leadstatus|        |---------------|      |-----------------| |1     |Japan   |      |1     | Hired    | |2     |China   |      |2     | Failed   | |3     |Korea   |      |3     | Hired    | |4     |Japan   |      |4     | Hired    | |5     |Japan   |      |5     | Hired    | 

My objective is to count the number of interviews per country and also count the number of hires and failed in each country. The resulting table should be like this

|Location|Interview|Hired|Failed| |-------------------------------| |Japan   | 3       |3    |0     | |Korea   | 1       |1    |0     | |China   | 1       |0    |1     | 

I have already done the counting of interviews per country. MY problem is I cannot count the number of hires and failed in each country. Here is my MySQL code as of the moment:

SELECT Location, count(*) as Interview  FROM table1  GROUP BY Location  ORDER BY Interview DESC 

Answer 1

This should work for you:

SELECT Location, COUNT(*) as Interview, SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired, SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed FROM table1  LEFT JOIN table2 ON table1.leadid = table2.leadid             GROUP BY Location              ORDER BY Interview DESC 

Here is a working sqlfiddle.

EDIT 2019: This can be simplified without using case statements, as the conditional statement itself returns a 1 or a 0, so you can simply SUM() on that:

SELECT Location, COUNT(*) as Interview, SUM(leadstatus = 'Hired') as Hired, SUM(leadstatus = 'Failed') as Failed FROM table1  LEFT JOIN table2 ON table1.leadid = table2.leadid             GROUP BY Location              ORDER BY Interview DESC 

Here is the updated sqlfiddle.

Answer 2

You can use conditional sum along with ranking system using the user defined variable as

select @rn:=@rn+1 as rank,  location, interview, hired, failed from(     select          t1.location,          count(*) as interview,         sum(t2.leadstatus='Hired') as hired,         sum(t2.leadstatus='Failed') as failed         from table1 t1         join table2 t2 on t1.leadid = t2.leadid          group by t1.location         order by interview desc )x,(select @rn:=0)y  order by rank ;