CODEWITHSUNDEEP
sql
cheezheadsrule
cheezheadsrule

Reputation: 1

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.

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

I'm trying to get a count total of 1

Upvotes: 0

Views: 1589

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

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

Upvotes: 2

Yuriy Faktorovich
Yuriy Faktorovich

Reputation: 68667

(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.

Upvotes: 3

Related Questions