Number of individual records - SQL

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 employees who work with only a few projects, based on the project identifier. I tried excellent and GROUP BY. but I am not able to pinpoint it exactly.

from the above table, expecting a result like this

  empid projectId 100 500 100 501 100 502 

Answer 1

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 ) 

That should give you

 EmpId ProjectId ----------- ----------- 100 500 100 501 100 502 3 row(s) 

Edit Content added for optional OPs question in comments

The account that distint ProjectIds gives you will mean that GROUP BY will be at EmpId level and there is no need for a subquery

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

To get the number 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 

Answer 2

You can also use 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 

Literature:

• OLAP features

• WITH clause

Answer 3

 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