SQL select total and split into success and failed

Have Tested This. Please find SQL FIDDLE LINK

SELECT 

    t1.leadid, 
    t1.Location, 
    count( t2.leadstatus ) Location, 
    count(case when t2.leadstatus = 'Hired' then t2.leadstatus end) as Hired, 
    count(case when t2.leadstatus = 'Failed' then t2.leadstatus end) as Failed  

FROM table1 AS t1 
    INNER JOIN table2 AS t2  
    ON t1.leadid = t2.leadid 

GROUP BY t1.Location,t2.leadstatus 
Order BY Hired DESC

FIDDLE

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 ;

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.

Simple conditional aggregation is all that is required here. As well as joining your two tables together:

select t1.location, count(*) as Interview, 
       count(case when t2.leadstatus = 'hired' then t2.leadstatus end) as Hired, 
       count(case when t2.leadstatus = 'failed' then t2.leadstatus end) as Failed
 from table1 t1
   inner join table2 t2
     on t1.leadid = t2.leadid
 group by t1.location

count() will only count non-null fields, and the case statement result is null when the condition is not met. Handy technique that applies in a lot of use cases.

This will only include locations for which there has been at least one interview. If you wish to include all countries, change the inner join to a left join.

demo here