CODEWITHSUNDEEP
mysqlsql
Conner
Conner

Reputation: 677

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

Upvotes: 0

Views: 72

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

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

Upvotes: 1

Related Questions