0

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.

  • Not clear. Do you want the sum of scores of all attempts for every student? The maximum score from every attempt of every student? The maximum sum from all (sums per student)? – ypercubeᵀᴹ Apr 6 '17 at 11:38
  • I am to sum the score for each student then find the student with the highest score. – tryllz Apr 6 '17 at 11:40
  • And which DBMS? And what if two (or more) students have the same highest sum? – ypercubeᵀᴹ Apr 6 '17 at 11:40
  • I already have summed up and found the value with the highest score but for some reason it wouldn't show just when used with MAX This query returns the highest score: 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; – tryllz Apr 6 '17 at 11:43
  • You didn't answer my questions. – ypercubeᵀᴹ Apr 6 '17 at 11:44
2

One way to accomplish what you're looking for is by using the ROWNUM pseudocolumn. Note that you need to define your ORDER BY in an inline view or by some other method to avoid a common trap that people fall into. Here's one way to do it:

SELECT *
FROM
(
    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    TOTAL_SCORE desc
) t
WHERE ROWNUM = 1;

As of Oracle 12c you can use the extended ORDER BY syntax to return just the first row. Here's an example:

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    TOTAL_SCORE desc
FETCH FIRST 1 ROWS ONLY;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.