CODEWITHSUNDEEP
sqldb2
zod
zod

Reputation: 12417

Count of distinct records - SQL

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

Upvotes: 6

Views: 43910

Answers (3)

Raj More
Raj More

Reputation: 48018

Try this (revised code)

SELECT DISTINCT EmpId, ProjectId
FROM TableX
WHERE EmpId IN 
(
    SELECT EmpId
    FROM TableX
    GROUP BY EmpId
    HAVING COUNT (DISTINCT ProjectId) > 1
)

This should give you

EmpId       ProjectId
----------- -----------
100         500
100         501
100         502

3 row(s)

Edit Content added for OPs additional question in the comments

A count giving you distint ProjectIds would mean that the GROUP BY would be at an EmpId level and no need for a subquery

SELECT EmpId, Count (Distinct ProjectId) Projects
FROM TableX
GROUP BY EmpId

To get a count of projects for all employees with multiple projects, do the following

SELECT EmpId, Count (Distinct ProjectId) Projects
FROM TableX
GROUP BY EmpId
Having Count (Distinct ProjectId) > 1

Upvotes: 6

Andriy M
Andriy M

Reputation: 77657

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:

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

SELECT y.empid, y.projectId
    FROM (SELECT empid
              FROM YourTable
              GROUP BY empid
              HAVING COUNT(*) > 1) t
        INNER JOIN YourTable y
            ON t.empid = y.empid
    ORDER BY y.empid, y.projectId

Upvotes: 0

Related Questions