I have a table which tracks the students performance in the courses.
| facultyID | academicYear | courseID | studentID | grade |
|---|---|---|---|---|
| 1 | 2022-2023 | 1 | 1 | A |
| 1 | 2022-2023 | 1 | 2 | A |
| 1 | 2022-2023 | 1 | 3 | A |
| 1 | 2022-2023 | 1 | 4 | B |
| 1 | 2022-2023 | 1 | 5 | B |
| 1 | 2022-2023 | 1 | 6 | C |
The first three columns forms a 'class', while the grade column can assume the following values (A, B, C, D and E).
I'm trying to build a query that counts how many times each grade value has occurred in each class, and the proportion of that grade value to the overall grade for that class.
The result will be:
| facultyID | academicYear | courseID | grade | gradeCount | percentage |
|---|---|---|---|---|---|
| 1 | 2022-2023 | 1 | A | 3 | 50 |
| 1 | 2022-2023 | 1 | B | 2 | 33.333 |
| 1 | 2022-2023 | 1 | C | 1 | 16.667 |
| 1 | 2022-2023 | 1 | D | 0 | 0 |
| 1 | 2022-2023 | 1 | E | 0 | 0 |
You need a CROSS join of all distinct classes to all distinct grades and a LEFT join to the table to aggregate:
WITH
classes AS (SELECT DISTINCT facultyID, academicYear, courseID FROM tablename),
grades AS (SELECT * FROM (VALUES ('A'), ('B'), ('C'), ('D'), ('E')) AS g(grade))
SELECT c.facultyID, c.academicYear, c.courseID, g.grade,
COUNT(t.grade) AS gradeCount,
ROUND(100.0 * COUNT(t.grade) / SUM(COUNT(t.grade)) OVER (PARTITION BY c.facultyID, c.academicYear, c.courseID), 3) AS percentage
FROM classes c CROSS JOIN grades g
LEFT JOIN tablename t
ON t.facultyID = c.facultyID AND t.academicYear = c.academicYear AND t.courseID = c.courseID AND t.grade = g.grade
GROUP BY c.facultyID, c.academicYear, c.courseID, g.grade
ORDER BY c.facultyID, c.academicYear, c.courseID, g.grade;
See the demo.