mysql - SQL选择总数，分为成功和失败

我有两张桌子

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    |

我的目标是计算每个国家/地区的面试次数，还计算每个国家/地区的录用人数和失败人数。结果表应该是这样的

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

我已经完成了每个国家/地区的采访计数。我的问题是我无法计算每个国家的雇用人数和失败人数。
到目前为止，这是我的MySQL代码:

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

最佳答案

这应该为您工作:

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是一个有效的sqlfiddle。

EDIT 2019:这可以不用使用case语句来简化，因为条件语句本身返回1或0，因此您可以简单地在其上使用SUM():

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是更新的sqlfiddle。