Search code examples
mysqlsqlgroup-byintersect

SQL query to get average per group in two tables

I have two tables: students and results.

For students, the fields are student_id, name, grade
For results, the fields are student_id, score

grade is like pre-school, one, two, three.... Like in a school.

As you see, student_id is in both tables.

I want a SQL query that returns average score per grade, like 

Pre-School => 50 
Kindergarten => 26

etc.

I tried group by with left joins but nothing works.

The query I've been working on is as follows:

SELECT 
    cat.grade, 
    dog.score 
FROM (SELECT * FROM students GROUP BY grade) AS cat, results AS dog 
GROUP BY cat.grade

But this just gives me one result per grade, so AVG() does not give the right answer here.

Solution

  • It should be as simple as joining the two table together, grouping on grade and selecting the average of all the scores.

    SELECT grade, 
       Avg(score) 
FROM   students 
       JOIN results using (student_id) 
GROUP  BY grade