Optimization of multiple aggregations in SELECT
CREATE TABLE Student (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(255) NOT NULL
);

CREATE TABLE Grade (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    StudentID INT NOT NULL FOREIGN KEY REFERENCES Student(ID),
    Score INT NOT NULL,
    CONSTRAINT CK_Grade_Score CHECK (Score >= 0 AND Score <= 100)
);

INSERT INTO Student (Name) VALUES ('Steven');
INSERT INTO Student (Name) VALUES ('Timmy');
INSERT INTO Student (Name) VALUES ('Maria');

INSERT INTO Grade (StudentID, Score) VALUES (1, 90);
INSERT INTO Grade (StudentID, Score) VALUES (1, 81);
INSERT INTO Grade (StudentID, Score) VALUES (1, 82);
INSERT INTO Grade (StudentID, Score) VALUES (1, 82);

INSERT INTO Grade (StudentID, Score) VALUES (2, 99);
INSERT INTO Grade (StudentID, Score) VALUES (2, 63);
INSERT INTO Grade (StudentID, Score) VALUES (2, 97);
INSERT INTO Grade (StudentID, Score) VALUES (2, 90);

INSERT INTO Grade (StudentID, Score) VALUES (3, 66);
INSERT INTO Grade (StudentID, Score) VALUES (3, 61);
INSERT INTO Grade (StudentID, Score) VALUES (3, 60);

SELECT Name,
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score < 65) AS 'F',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 65 AND Score < 70) AS 'D',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 70 AND Score < 80) AS 'C',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 80 AND Score < 90) AS 'B',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 90 AND Score <= 100) AS 'A'
FROM Student

Name    F     D     C     B     A
-----------------------------------------
Steven  NULL  NULL  NULL  81    90
Timmy   63    NULL  NULL  NULL  95
Maria   60    66    NULL  NULL  NULL

SELECT st.name,
       avg(CASE WHEN g.score < 65 THEN g.score ELSE NULL END) as F,
       avg(CASE WHEN g.score >= 65 AND g.score < 70 THEN g.score ELSE NULL END) as D,
       avg(CASE WHEN g.score >= 70 AND g.score < 80 THEN g.score ELSE NULL END) as C,
       avg(CASE WHEN g.score >= 80 AND g.score < 90 THEN g.score ELSE NULL END) as B,
       avg(CASE WHEN g.score >= 90 AND g.score <= 100 THEN g.score ELSE NULL END) as A
FROM Grade g
  JOIN Student st ON g.studentid = st.ID
GROUP BY st.name

;WITH
Scores(ID,Score) AS(
    SELECT S.ID,AVG(Score)
    FROM Student S
    JOIN Grade G
        ON S.ID = G.StudentID
    GROUP BY S.ID)

SELECT ST.Name
    ,CASE WHEN S.Score  < 65 THEN S.Score ELSE NULL END AS 'F'
    ,CASE WHEN S.Score  BETWEEN 65 AND 70 THEN S.Score ELSE NULL END AS 'D'
    ,CASE WHEN S.Score  BETWEEN 70 AND 80 THEN S.Score ELSE NULL END AS 'C'
    ,CASE WHEN S.Score  BETWEEN 80 AND 90 THEN S.Score ELSE NULL END AS 'B'
    ,CASE WHEN S.Score  BETWEEN 90 AND 100 THEN S.Score ELSE NULL END AS 'A'
FROM Scores S
JOIN Student ST
    ON S.ID = ST.ID

SELECT s.Name
    ,SUM(CASE Score_g WHEN 'F' THEN Score_avg END) as 'F'
    ,SUM(CASE Score_g WHEN 'D' THEN Score_avg END) as 'D'
    ,SUM(CASE Score_g WHEN 'C' THEN Score_avg END) as 'C'
    ,SUM(CASE Score_g WHEN 'B' THEN Score_avg END) as 'B'
    ,SUM(CASE Score_g WHEN 'A' THEN Score_avg END) as 'A'
FROM Student s,
     (
      SELECT StudentId, score_g, avg(score) as score_avg
      FROM  (
            SELECT StudentID, Score
            CASE
              WHEN Score < 65                   THEN 'F'
              WHEN Score >= 65 AND Score < 70   THEN 'D'
              WHEN Score >= 70 AND Score < 80   THEN 'C'
              WHEN Score >= 80 AND Score < 90     THEN 'B'
              WHEN Score >= 90 AND Score <= 100 THEN 'A'
              ELSE 'X'
            END AS Score_g
            FROM Grade
        ) g
       GROUP BY StudentId, score_g
    ) t
WHERE s.ID = t.StudentID
GROUP BY s.Name

SELECT s.name
    ,AVG(CASE WHEN Score < 65                   THEN SCORE END) AS 'F'
    ,AVG(CASE WHEN Score >= 65 AND Score < 70   THEN SCORE  END) AS 'D'
    ,AVG(CASE WHEN Score >= 70 AND Score < 80   THEN SCORE  END) AS 'C'
    ,AVG(CASE WHEN Score >= 80 AND Score < 90     THEN SCORE END) AS 'B'
    ,AVG(CASE WHEN Score >= 90 AND Score <= 100 THEN SCORE  END) AS 'A'
FROM Grade g, Student s
WHERE g.StudentID = s.ID
GROUP BY s.name

;WITH marked AS (
  SELECT
    StudentID,
    Score,
    Mark = CASE
      WHEN Score < 65 THEN 'F'
      WHEN Score < 70 THEN 'D'
      WHEN Score < 80 THEN 'C'
      WHEN Score < 90 THEN 'B'
      ELSE 'A'
    END
  FROM Grade
),
pivoted AS (
  SELECT
    StudentID,
    F, D, C, B, A
  FROM marked m
  PIVOT (
    AVG(Score) FOR Mark IN (F, D, C, B, A)
  ) p
)
SELECT
  s.Name,
  p.F,
  p.D,
  p.C,
  p.B,
  p.A
FROM Student s
  INNER JOIN pivoted p ON s.ID = p.StudentID