[SOLVED] SQL query : unique studentid that occur with other studentid in other project for more than 10 times

Issue

I have a project table which contains columns studentid, projectid. The students worked in group to complete the project and a students is allowed to participate in more than one project. I wish to query on how many students have partnered up together on 10 or more project.

studentid projectid
1 abx
2 abx
3 abx
1 abn
4 abn

How do I do a nested query to find out each unique studentid have occurrence with other studentid for more than 10 times?

Solution

First self-join the table in order to get the students that worked together. Then aggregate and count.

In order not to look at the same pair twice (s1/s2 <=> s2/s1), I don’t join on s1 <> s2, but on s1 < s2.

select t1.studentid, t2.studentid, count(*)
from mytable t1
join mytable t2 on t2.projectid = t1.projectid and t2.studentid > t1.studentid
group by t1.studentid, t2.studentid
having count(*) >= 10;

Answered By – Thorsten Kettner

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.