user2827048 user2827048 - 1 month ago 8
SQL Question

Use avg() and joins mysql

Say I have a table called students

idStudent Name

1 Billy
2 Mariah
3 Chris
4 Mark
5 Sarah


and another table called tests

idTest score student_idstudent

1 50 1
2 100 1
3 90 2
4 100 3
5 45 4


is it possible to use a combination of a join and avg() to get a result like

idStudent avg_test
1 75
2 90
3 100
4 45
5 0

Answer
SELECT s.idStudent,
       AVG(COALESCE(t.score, 0)) AS avg_test
FROM students s
LEFT JOIN tests t
    ON s.idStudent = t.student_idStudent
GROUP BY s.idStudent
Comments