I have one large table with student ID and grade1 from more than one year, and grade2 also could be multiple times, need to calculate the average of the max grade1 with the max grade2 for each student and display in one table as final grade. Example

Student   Grade   score
Alex      grade1  80
Joe       grade1  70
Alex      grade1  90
Joe       grade2  60
Alex      grade2  70
Joe       grade2  80
Alex      grade2  50

What I want is like this:

Student   final_score
Alex       80
Joe        75

I took the max of each grade and calculated average. Can I do this in one Query? How to do this?

CodePudding user response：

Since you have multiple steps to aggregate (first get max, then get average) you'll need two SELECT statements as you can only perform a single level aggregation per SELECT:

1. Get the max for each grade into a result set:

    SELECT Student, Grade, Max(score) as maxscore FROM yourtable GROUP BY Studen, Grade;
   

2. Calculate the average of this result set for all grades:

     SELECT Student, Avg(maxscore) as avgscore
     FROM 
         (SELECT Student, Grade, Max(score) as maxscore FROM yourtable GROUP BY Studen, Grade) dt
     GROUP BY Student;