SQL Count Number Of Classmates For a Student Across All Courses

Question

I have the following tables:

• Student: Student_ID (PK), Student_Name
• Course: Course_ID (PK), Course_Name
• Enrollment: Student_ID (FK), Course_ID (FK)

I need 2 queries:

1. A query that computes for each student id in the Student table the total number of different (Unique) classmates that this student has across all courses. If the student is not enrolled in any courses 0 should be returned.

   For example if Student_ID 123 is enrolled in 3 courses and he has 10 different classmates in each of those courses, I should get the following result:

   Student_ID        Num_of_classmates
   -----------------------------------
   123               30
   

2. A SQL query that returns all students with all of their classmates. classmate of a student is another student who is enrolled in at least one same class.

Answer 1

It is unclear which of these you want:

• Total students in all the classes (which would include a given student).
• Total distinct students, since some classmates might be in multiple classes.
• Total distinct students not including the given student ("I am not my own classmate").

In any case, the idea is basically two joins and aggregation:

select s.student_id,
       count(ec.student_id) as total_classmates,
       count(distinct s.student_id) as distinct_classmates,
       count(distinct case when ec.student_id <> s.student_id then ec.student_id end) as distinct_other_classmates
from student s left join
     enrollment e
     on e.student_id = s.student_id left join
     enrollment ec
     on ec.class_id = e.class_id;
group by s.student_id;

Answer 2

Here I only give a solution to part 2) as @Gordon Linoff has done part 1) and you have also fixed the -1 problem.

A point: use inner join instead of left join here to avoid NULL in classmate names. Hopefully this also adds a little bit of help :)

Test dataset

if object_id('tempdb..#Student') is not null drop table #Student;
create table #Student (
    Student_ID int PRIMARY key,
    Student_Name varchar(50)
)
insert into #Student(Student_ID, Student_Name)
values (1,'Alice'), (2,'Bob'),(3,'Claire'),(4,'Danny'),(5,'Eve'),(6,'Frank');

if object_id('tempdb..#Course') is not null drop table #Course;
create table #Course (
    Course_ID int PRIMARY key,
    Course_Name varchar(50)
)
insert into #Course(Course_ID, Course_Name)
values (1,'Algorithm'), (2,'Bash'),(3,'Compiler'),(4,'Design Pattern'),(5,'Exception Handling');

if object_id('tempdb..#Enrollment') is not null drop table #Enrollment;
create table #Enrollment (
    Student_ID int,
    Course_ID int
)
insert into #Enrollment(Student_ID, Course_ID)
values (1,1),(1,3),
       (2,2),(2,3),
       (3,3),(3,4),
       (4,1),(4,4),
       (5,1),
       (6,5);  -- This Frank guy has no classmate

-- select * from #Student;
-- select * from #Course;
-- select * from #Enrollment;

Solution to 2)

select distinct
    A.Student_Name as Student_Name,
    D.Student_Name as Classmate_Name
from #Student as A
-- Student (A) -> Enrolled Courses (B)
inner join #Enrollment as B  
    on A.Student_ID = B.Student_ID
-- Enrollment Courses (B) -> Enrolled Students in that Course (C)
inner join #Enrollment as C  
    on B.Course_ID = C.Course_ID
    and B.Student_ID <> C.Student_ID  -- exclude self
-- Classmate's names
inner join #Student as D
    on C.Student_ID = D.Student_ID
order by Student_Name, Classmate_Name;

Output

N.B. Frank has no classmates and do not show a NULL value.

| Student_Name | Classmate_Name |
|--------------|----------------|
| Alice        | Bob            |
| Alice        | Claire         |
| Alice        | Danny          |
| Alice        | Eve            |
| Bob          | Alice          |
| Bob          | Claire         |
| Claire       | Alice          |
| Claire       | Bob            |
| Claire       | Danny          |
| Danny        | Alice          |
| Danny        | Claire         |
| Danny        | Eve            |
| Eve          | Alice          |
| Eve          | Danny          |