How to get Count under specific condtion in addition to the main conditions?

Question

I don't know if this is possible, but i want to get a count under a specific condition which is different from the main query.

If i have Two tables like this :

    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

My Query

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

Now i want to get in the same previous query also the number of shifts(count) with shift_id =14 for each employee in the same query.(This condition is belong only for this part of data)

The expected result for the example :

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

     18          Maria        3                 2

Add some sample table data and the expected result (as formatted text, not images.) — jarlh, Oct 03 '17 at 09:00

score 2 · Answer 1 · answered Oct 03 '17 at 09:03

You can try this.

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

score 2 · Accepted Answer · answered Oct 03 '17 at 09:07

You can add a new case clause in your query

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

How to get Count under specific condtion in addition to the main conditions?

2 Answers2