Count with multiple where conditions

I thought this would have been really easy but I'm missing something.

Here's my data:

ID       EMPID    Index1
314      021576   5
315      021576   2
317      021576   8
318      021576   12
500      021576   398
501      021576   388
502      021111   4
503      021111   8 

Here's my SQL statement.开发者_如何学C

SELECT COUNT(DISTINCT EmpID) AS Expr1
    FROM ProfileData
    WHERE (Index1ID = 2) AND (Index1ID = 5)

I'm trying to get a count total of 1

(Index1ID = 2) AND (Index1ID = 5)

Is a where clause which will always return false. If you mean for it to be OR, it would still not work, since it would return 2.

My guess is that you want to know the number of EMPID values that have both a row with an Index1ID value of 2 and an Index1 value of 5 (EMPID 021576 has both, EMPID 021111 has neither). There are a variety of ways to do this

Using set operations

SELECT COUNT(DISTINCT empid)
  FROM (SELECT empid
          FROM ProfileData
         WHERE Index1ID = 2 
        INTERSECT
        SELECT empid
          FROM ProfileData
         WHERE Index1ID = 5)

Or using a GROUP BY

SELECT COUNT(DISTINCT empid)
  FROM (SELECT empid, COUNT(DISTINCT Index1ID) cnt
          FROM ProfileData
         WHERE Index1ID IN (2,5)
         GROUP BY empid)
 WHERE cnt = 2

If you wanted the list of EMPID values that are associated with both Index1ID values, that's easier with the HAVING clause

SELECT empid, COUNT(DISTINCT Index1ID)
  FROM ProfileData
 WHERE Index1ID IN (2,5)
 GROUP BY empid
HAVING COUNT(DISTINCT Index1ID) = 2