Count and IN Clause Issue

Question

I have a table named as employee history and its structure is as follows.

tbl_employee_history Structure

id|history_nr|history_value|employee_id
1   82         83             1
2   86         84             1 
3   87         85             1
4   603                       1
5   82         83             2
6   86         83             2
7   87         83             2
8   603        83             2

This is the dummy data for my table. Now I want to count all those employees whose history_nr is in 82,86,87,603 AND history_value should not be empty like in the above example the count should be 1 as for employee id 2 all the values are not empty. This is my query for implementing the count.

SELECT count(employee_id) FROM tbl_employee_history where history_nr IN(82,86,87,603) AND history_value!=''

But what happens is the count returns me two values. Thanks in advance.

Answer 1

Remove your distinct() portion of your query, this will return only unique values. As you have 4 rows each from employee_id 1 and 2, it will only grab the first rows with distinct (unique) employee_id.

Thus it only pulls 2 records, one for each employee_id.

SELECT count(employee_id) FROM tbl_employee_history where history_nr IN(82,86,87,603) AND history_value != ''
//Returns 7 Rows

SELECT count(distinct(employee_id)) FROM tbl_employee_history where history_nr IN(82,86,87,603) AND history_value != ''
//Returns 2 Rows