CODEWITHSUNDEEP
mysqlsqldatabase
H. Wilde
H. Wilde

Reputation: 197

How to find the count of workers working on the exact same projects

Given the following table where both rows make up the key:

WORKS_ON: ID, ProjectNum

Is it possible to output for each ID, the number of other workers (each ID corresponds to a worker) who also work on the exact same projects?

For example if we have ID 1 working on projects 2 and 3, 2 working on 2 and 3 working on 2 and 3. I want to output 1 with 1 as the count, 2 with 0 as the count and 3 with 1 as the count.

I am not sure if this is even possible, I did think of a naive way whereby I select the count of rows for each ID and then order by ProjectNumber before comparing all of the ProjectNumber's one by one. But I am not sure how to then count the number of employees satisfying this or how to formulate it in an acceptable way.

Any guidance is appreciated.

Upvotes: 1

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can express this rather convolutedly using set mechanisms in SQL. Or, you can do it more simply using string aggregation. So, in MySQL, this would look like:

select i.id, count(i2.id) as cnt
from (select id, group_concat(projectnum order projectnum) as projects
      from works_on
      group by id
     ) i left join
     (select id, group_concat(projectnum order projectnum) as projects
      from works_on
      group by id
     ) i2
     on i2.projects = i.projects and i2.id <> i.id
group by i.id
order by i.id;

Upvotes: 2

Related Questions