查找班级平均水平和每个学科的学生人数

如何获得每个科目的平均班级列和No. of students提供每个科目的平均列？我创建了以下表格语句和查询。以下是我的查询，该查询为我提供了一个没有班级平均水平和No. of students每个学科的学生的结果。

表格学生有三列，其中包含三个学生

Studentid   Firstname   Lastname
--------------------------------
1          Oreofeoluwa  Ogunkoya
2          Prevailer    Adebayo
3          Arike        Adeladan
4          Khalilat     Yakubu

表课程包含四个课程，Irk和Crk是可选的。

Courseid    Course
------------------
1           Maths
2           English
3           Irk
4           Crk     

我也有StudentCourse作为课程和学生的联系表。它包含所有学生的分数。

SELECT Course
    ,Score
    ,Grade
    ,Comment
    ,Pos
    ,Minimum
    ,Maximum
FROM (
    SELECT S.firstname
        ,S.lastname
        ,C.course
        ,Sc.score
        ,CASE 
            WHEN Score BETWEEN 80
                    AND 100
                THEN 'A'
            WHEN Score BETWEEN 70
                    AND 79
                THEN 'B'
            WHEN Score BETWEEN 60
                    AND 69
                THEN 'C1'
            WHEN Score BETWEEN 50
                    AND 59
                THEN 'C2'
            WHEN Score BETWEEN 40
                    AND 49
                THEN 'D'
            ELSE 'F'
            END AS Grade
        ,CASE 
            WHEN Score BETWEEN 80
                    AND 100
                THEN 'Excellent'
            WHEN Score BETWEEN 70
                    AND 79
                THEN 'Very Good'
            WHEN Score BETWEEN 60
                    AND 69
                THEN 'Good'
            WHEN Score BETWEEN 50
                    AND 59
                THEN 'Average'
            WHEN Score BETWEEN 40
                    AND 49
                THEN 'Pass'
            ELSE 'Fail'
            END AS Comment
        ,Rank() OVER (
            PARTITION BY course ORDER BY Score DESC
            ) AS Pos
        ,Min(Score) OVER (
            ORDER BY course
            ) AS Minimum
        ,Max(Score) OVER (
            ORDER BY course
            ) AS Maximum
    FROM Student S
    JOIN Studentcourse Sc ON S.Studentid = Sc.Studentid
    JOIN Courses C ON C.courseid = Sc.Courseid
    ) sub
WHERE firstname = 'Oreofeoluwa'

该查询为我提供了下表，但我需要全班平均水平，没有提供每个学科的学生人数

Course  Score   Grade   Comment Pos Minimum Maximum
---------------------------------------------------
Crk     62.00   C1      Good    1   44.00   62.00
English 80.00   A   Excellent   1   43.00   80.00
Maths   96.00   A   Excellent   1   36.00   96.00