How to select max value from the output of another query-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.