Question 113196 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to select max value from the output of another query

I have a table as below:

id english hindi maths science
1 80 76 90 79
2 8 63 80 69
3 50 50 80 69
4 80 80 80 69
5 80 50 70 69

I wrote a query to get total of all the marks for each student 

SELECT SUM(english+hindi+maths+science) AS total FROM MARKS GROUP BY id);

Got the following result as expected

total
325
220
249
309
265

Now I am trying to query the maximum marks from total. I have tried the below code:

SELECT MAX(total) 
from (SELECT SUM(english+hindi+maths+science) AS total 
        FROM MARKS 
        GROUP BY id);

But the code returns error, can anyone please explain why this doesn’t work in mySQL?

the error is

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

Tags:

2

Answers


  1. 0

    Your group by and sum makes no sense. You use sum to sum the values in a column, not a row. Here’s a solution using rank() that also provides you the id of the max(total). If you just want the max(total) you can use the second solution.

    select    id 
         ,total_score
from     (
         select    id
                  ,english+hindi+maths+science as total_score
                  ,rank() over(order by english+hindi+maths+science desc) as rnk
         from     t
         ) t
where    rnk = 1
    id total_score
    1 325 
     select   max(english+hindi+maths+science) as max_total_score
 from     t
    total_score
    325

    Fiddle

    Login or Signup to reply.
  2. 0

    You’re getting that error because you’re missing the alias in your subquery, as already mentioned in the comments. Adding the alias will fix it:

    SELECT MAX(total) 
from (SELECT SUM(english+hindi+maths+science) AS total 
        FROM MARKS 
        GROUP BY id) theMissingAlias;

    One more option to solve this task is to use the LIMIT clause in combination with the ORDER BY clause to determine the biggest summed up value.

    SELECT id,
       english + hindi + maths + science AS total
FROM tab
ORDER BY total DESC
LIMIT 1

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search