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

Ответ 1

Это должно работать для вас:

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

Здесь - работающий sqlfiddle.

ОБНОВЛЕНИЕ 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

Ответ 2

Вы можете использовать условную сумму вместе с системой ранжирования с использованием определяемой пользователем переменной как

select
@rn:[email protected]+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 ;

Ответ 3

Протестировали это. 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

Ответ 4

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;

где первая таблица loc содержит лидерство и местоположение, вторая таблица статус, содержащая leadid и статус

Ответ 5

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;

скрипт - http://sqlfiddle.com/#!9/269da/14

Ответ 6

Простая условная агрегация - это все, что здесь требуется. Как и объединение двух таблиц вместе:

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() будет считать только ненулевые поля, а результат выражения case равен null, когда условие не выполняется. Удобная техника, применяемая во многих случаях использования.

Это будет включать только те места, где было по крайней мере одно интервью. Если вы хотите включить все страны, измените inner join на left join.

демо здесь