Count of distinct records - SQL



Answers

You could also use a windowed COUNT():

WITH counted AS (
  SELECT
    empid,
    projectId,
    COUNT(DISTINCT projectId) OVER (PARTITION BY empid) AS ProjectCount
  FROM atable
)
SELECT DISTINCT
  empid,
  projectId
FROM counted
WHERE ProjectCount > 1

References:

Question
empid   projectId   TaskID
100     500           1
100     501           1
100     502           1
101     500           2
101     500          5
101     500          1
102     400          1
103     300          2
104     300          2
105     300          2  

I am trying to list the employees who works on multiple project only, based on project id . I tried distinct and GROUP BY . but am not able figure it exactly.

from the above table am expecting a result like this

 empid   projectId  
    100     500         
    100     501          
    100     502 



Links



Tags