Count number of students per one role SQL

I have a table students with student id, course id and role number looking like this (ordered by student_id):

student_id    course_id    role
8061          7787         18
8061          7788         18
9122          7734         18
9122          1028         18
9122          1036         18
9122          7734         18
9122          7740         18
8701          9829         18
8701          9829         30
8701          1031         18
8701          1031         30
...

How to count number of students for each role? If student is enrolled in multiple courses with the same role number, then count this as one. The expected output for above table would be similar to this:

role    num_students
18      25
30      134
...

So in the above table sample, the partition would be by student_id:

student_id    course_id    role
-------------------------------
8061          7787         18
8061          7788         18      num_students for role 18: +1
-------------------------------
9122          7734         18
9122          1028         18
9122          1036         18
9122          7734         18
9122          7740         18      num_students for role 18: +1
-------------------------------
8701          9829         18
8701          9829         30
8701          1031         18      num_students for role 30: +1
8701          1031         30      num_students for role 18: +1
-------------------------------
                                   sum role 18: 3
...                                sum role 30: 1

I’m not sure if this is possible to achieve with the current table. I have implemented PARTITION BY, but I can only count number of distinct roles per students, which is not correct:

SELECT s.student_id, s.role, COUNT(s.student_id) OVER (PARTITION BY s.student_id) AS num_students
FROM students s
GROUP BY s.student_id, s.role
ORDER BY s.role;

The above query outputs something like this (I included roles to illustrate the error):

student_id    role     num_students
8061          18       1
9122          18       1
8701          18       2            since there two distinct roles 
8701          30       2
...

Answer

Aggregate by role and take the distinct count of students:

SELECT role, COUNT(DISTINCT student_id) AS num_students
FROM students
GROUP BY role;

You seem to want to report each role only once, which implies that the GROUP BY option above is what you want. Note that using COUNT as an analytic function will result in every record from the original students table being reported, which is not what you want here.

Advertisement

Answer