E. Molnar E. Molnar - 1 year ago 51
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download