SQL Server Determining grade distribution by class

c3frrgcw 于 2023-06-04 发布在其他

关注(0)|答案(1)|浏览(218)

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 |

sql-server

来源：https://stackoverflow.com/questions/76347974/determining-grade-distribution-by-class

1条答案

按热度按时间

bybem2ql1#

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 .

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Determining grade distribution by class

1条答案

相关问题

热门标签

最新问答