CODEWITHSUNDEEP
mysqlsql
marse
marse

Reputation: 873

SQL select total and split into success and failed

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

Upvotes: 22

Views: 10211

Answers (6)

Blue
Blue

Reputation: 22911

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.

Upvotes: 21

Shal
Shal

Reputation: 633

Select location,count(*) as Interview,
SUM(CASE WHEN (status='Hired')Then 1 Else 0 END) as Hired,
SUM(CASE WHEN(status='Failed') Then 1 Else 0 END) as Failed 
from loc inner join status on loc.leadid= status.leadid 
group by location;

where first table is loc containg leadid and location,second table is status containing leadid and status

Upvotes: 4

Nilesh Thakkar
Nilesh Thakkar

Reputation: 1459

SELECT table1.location, COUNT(*) as Interview, 
COUNT(CASE WHEN table2.leadstatus = 'hired' THEN table2.leadstatus END) as Hired, 
COUNT(CASE WHEN table2.leadstatus = 'failed' THEN table2.leadstatus END) as Failed
FROM table1 
INNER JOIN table2 ON table1.leadid = table2.leadid
GROUP BY table1.location
ORDER BY Interview DESC;

fiddle - http://sqlfiddle.com/#!9/269da/14

Upvotes: 3

kamlesh.bar
kamlesh.bar

Reputation: 1804

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

Upvotes: 5

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

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 ;

Upvotes: 8

pala_
pala_

Reputation: 9010

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

Upvotes: 4

Related Questions