I have a table students with student id, course id and role number looking like this (ordered by student_id):
student_id course_id role8061 7787 188061 7788 189122 7734 189122 1028 189122 1036 189122 7734 189122 7740 188701 9829 188701 9829 308701 1031 188701 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_students18 2530 134 So in the above table sample, the partition would be by student_id:
student_id course_id role-------------------------------8061 7787 188061 7788 18 num_students for role 18: +1-------------------------------9122 7734 189122 1028 189122 1036 189122 7734 189122 7740 18 num_students for role 18: +1-------------------------------8701 9829 188701 9829 308701 1031 18 num_students for role 30: +18701 1031 30 num_students for role 18: +1------------------------------- sum role 18: 3 sum role 30: 1I’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_studentsFROM students sGROUP BY s.student_id, s.roleORDER BY s.role; The above query outputs something like this (I included roles to illustrate the error):
student_id role num_students8061 18 19122 18 18701 18 2 since there two distinct roles 8701 30 2 Advertisement
Answer
Aggregate by role and take the distinct count of students:
SELECT role, COUNT(DISTINCT student_id) AS num_studentsFROM studentsGROUP 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.