Anyname Donotcare Anyname Donotcare - 1 month ago 116
SQL Question

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

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

Answer Source

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