I need to SUM the total values of each student using INNER JOIN and then find the row with maximum value.
I have used the below query to find the SUM of each student and sort in descending order SHOWING ALL the students including the one with the maximum value.
Select S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE" from Student S inner join Attempt A on S.Student_ID = A.Student_ID group by S.Student_ID, S.Student_Name order by 3 desc;
I need to ONLY show the student with the maximum value but it shows an empty table.
Select S.Student_ID, S.Student_Name, MAX(A.Score) as "TOTAL SCORE" from Student S inner join Attempt A on S.Student_ID = A.Student_ID where A.Score = (select SUM(A.Score) from Student S) group by S.Student_ID, S.Student_Name;`
Does this have to be done with MAX or is there another way to do it, if latter then how.
Not sure if I can explain simpler than this.