除了主要条件外，如何在特定的条件下计算计数？

Question

我不知道这是否可能，但我希望在与主查询不同的特定条件下获得计数。

如果我有两张这样的桌子：

    Overtime 

emp_num,  dep_code,  trans_date,   overtime           shift_id
 13         305      2017-9-1      02:27:00.0000000      12
 13         305      2017-9-2      02:21:00.0000000      14
 13         305      2017-9-4      01:21:00.0000000      13
 18         305      2017-9-4      06:21:00.0000000      14
 18         305      2017-9-10     09:21:00.0000000      14
 18         305      2017-9-18     08:21:00.0000000      13

Employee

emp_num,    name
13          Joe
18          Maria

我的疑问

SELECT a.emp_num ,b.NAME,COUNT(*) AS 'Num of days'
from Overtime  a INNER JOIN Employee b
ON a.emp_num = b.emp_num
WHERE a.dep_code = 305 AND trans_date BETWEEN '2017-9-1' AND '2017-9-30'
and over_time >= '02:00:00.0000000'
GROUP BY a.emp_num ,b.NAME

现在，我希望获得相同的前一个查询，以及同一个查询中每个员工使用shift_id =14的轮班数(计数)。(此条件只属于数据的这一部分)

该示例的预期结果：

   emp_num       Name        Num of days     Num of shifts
     13          Joe          2                 1

     18          Maria        3                 2

Answer 1

可以在查询中添加新的case子句。

SELECT 
 a.emp_num ,
 b.NAME,
 COUNT(*) AS 'Num of days',
 SUM(CASE WHEN Shift_id=14 THEN 1 ELSE 0 END ) as 'Number of days with Shift id 14'
from Overtime  a INNER JOIN Employee b
ON a.emp_num = b.emp_num
WHERE a.dep_code = 305 AND trans_date BETWEEN '2017-9-1' AND '2017-9-30'
and over_time >= '02:00:00.0000000'
GROUP BY a.emp_num ,b.NAME

Answer 2

你可以试试这个。

SELECT 
    a.emp_num ,
    b.NAME,COUNT(*) AS 'Num of days', 
    COUNT(CASE WHEN shift_id =14 THEN shift_id ELSE NULL END) AS 'Num of shifts'
from 
    Overtime  a 
    INNER JOIN Employee b ON a.emp_num = b.emp_num
WHERE a.dep_code = 305 AND trans_date BETWEEN '2017-9-1' AND '2017-9-30'
and over_time >= '02:00:00.0000000'
GROUP BY a.emp_num ,b.NAME