E. Molnar E. Molnar - 4 months ago 6
MySQL Question

SQL Averaging max values with nested Order By

I have student testing records in a SQL database where individual students could have multiple scores. I would like to find the highest score (max) for each student and them summarize that data by grade level (order by). I tried nesting "order by" but it did not work. And when I do a max(avg(score)) I get an error.

Example Record:



studentID gradeLevel score
student_1 10 495
student_1 10 365
student_2 9 652
student_2 9 589
student_3 10 612
student_3 10 485
student_4 9 358
student_4 9 600


Desired results:



Grade_9 626 (652+600)/2
Grade_10 554 (495+612)/2


Any help and/or pointing in the right direction would be much appreciated.

Answer

Here's one option using a subquery:

select gradelevel, avg(maxscore)
from (
  select studentid, gradelevel, max(score) maxscore
  from yourtable
  group by studentid, gradelevel
  ) t
group by gradelevel
Comments