Partition by two columns and find sum SQL

Question

Assume there's a table students where we have student id, course id, role id, number of failed assignments and total number of assignments of the students in the course.

student    role    course    fail    total 
1000       23      20022     1       5
1000       23      10055     2       8
1000       23      29000     0       10        fail = 2, total = 23 
           -------------------------------
1000       15      50003     1       7
1000       15      10299     3       8         fail = 4, total = 15
           -------------------------------
1000       34      30042     0       5         fail = 0, total = 5
------------------------------------------
2035       34      90002     1       10
2035       34      55053     2       8         fail = 3, total = 18
           -------------------------------
2035       10      80003     0       5         fail = 0, total = 5
...

What is the way to partition by two columns to find total number of fails and total number of assignments per each role for each student?

Expected output for above example would be:

student    role    sum_fail    sum_total 
1000       23      2           23
1000       15      4           15 
1000       34      0           5
----------------------------------------
2035       34      3           18
2035       10      0           5
...

The code I tried so far produces incorrect numbers where each student have exactly the same number of fails and total per role:

SELECT s.student, s.sum_fail, s.sum_total 
FROM ( SELECT student, role, SUM(fail) OVER (PARTITION BY role) AS sum_fail,
                             SUM(total) OVER (PARTITION BY role) AS sum_total
       FROM students ) s
WHERE s.student=student
GROUP BY s.student, s.sum_fail, s.sum_total;

user_2270762 · Answer

You want one result row per student and role. "per" translates to GROUP BY in SQL. With two mere sums, this is a simple aggregation:

select student, role, sum(fail) as sum_fail, sum(total) as sum_total
from students
group by student, role
order by student, role;

Partition by two columns and find sum SQL

1 Answers