Calculate 2 average values based on the same column, with different conditions

Question

I have the following tables:

Students (id, name, surname, study_year, department_id)

Courses(id, name)

Course_Signup(id, student_id, course_id, year)

Grades(signup_id, grade_type, mark, date), where grade_type can be 'e' (exam), 'l' (lab) or 'p' (project)

I want to display the average grade at the exam and the lab+project, for each student.

SELECT new_table.id,
       new_table.name,
       new_table.grade_type,
       AVG(new_table.mark) AS "Average Exam Grade"
  FROM (SELECT s.id, c.name, g.grade_type, g.mark
          FROM Students s
          JOIN Course_Signup csn
            ON s.id = csn.student_id
          JOIN Courses c
            ON c.id = csn.course_id
          JOIN Grades g
            ON g.signup_id = csn.id) new_table
 GROUP BY new_table.id, new_table.name, new_table.grade_type
HAVING new_table.grade_type = 'e'
 ORDER BY new_table.id ASC

This will give me the average exam grade for each student, for every course they signed up for, but I want to also have an AVG(new_table.mark) AS "Average Activity Grade" which would be calculated based on the marks present in columns with grade_type = 'l' or grade_type = 'p'. Since I already have the exam grades condition in HAVING, how can I add the second condition for the second AVG?

Answer 1

Apply the filter inside the AVG() using CASE expressions.

SELECT
  new_table.id,
  new_table.name,
  AVG(CASE WHEN grade_type  = 'e' THEN new_table.mark END) AS "Average Exam Grade",
  AVG(CASE WHEN grade_type <> 'e' THEN new_table.mark END) AS "Average Activity Grade" 
FROM
(
  SELECT s.id, c.name, g.grade_type, g.mark
    FROM Students s
    JOIN Course_Signup csn
         ON s.id = csn.student_id
    JOIN Courses c
         ON c.id = csn.course_id
    JOIN Grades g
         ON g.signup_id = csn.id
)
  new_table
WHERE
  new_table.grade_type IN ('e', 'l', 'p')
GROUP BY
  new_table.id,
  new_table.name
ORDER BY
  new_table.id ASC

This works because...

• CASE returns NULL (in the absences of an ELSE block) if not matched.
• Aggregates (such as AVG()) skip / ignore NULL values.

Note; there's no need for your sub-query, I left it in because you had it, but it is certainly redundant.