0

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.

user2216168
  • 21
  • 1
  • 1
  • 4
  • I made a mock up of your database and query, I get back a count of 7. Is there any more to your code? It sounds like you are either grouping or looking for distinct values. – Jason Aug 28 '13 at 18:30
  • Yes I am looking for distinct values, I want to select those distinct employees whose all history values that are mentioned in the in clause should not be empty if any value mentioned in the in clause is empty then it should not be considered. The query is select count(distinct(employee_id)) Where history_Value in (82,86,603,87) and history_value!='' – user2216168 Aug 28 '13 at 18:58

1 Answers1

0

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
Jason
  • 1,987
  • 1
  • 14
  • 16
  • I have implemented the second query in my system but the problem is and it returns me count of two from the above data. What I want to achieve here is that if any of the history_nr value is empty like at id 4 for employee 1 is empty it should not even consider that employee in the count. So the correct count is 1 instead of 2. – user2216168 Aug 29 '13 at 11:05