1

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

lemon
  • 14,875
  • 6
  • 18
  • 38
Adi
  • 11
  • 3
  • apologies for formatting error in "total" table which is the output of first code – Adi Oct 13 '22 at 16:08
  • So if you are getting an error SHOW the error to us, dont make us guess – RiggsFolly Oct 13 '22 at 16:08
  • 1
    Does this answer your question? [What is the error "Every derived table must have its own alias" in MySQL?](https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql) The issues seems like it is because your sub-query doesn't have its own alias. To fix that, do `SELECT MAX(total) from (SELECT SUM(english+hindi+maths+science) AS total FROM MARKS GROUP BY id) totals;`. See http://sqlfiddle.com/#!9/c2e169/5 for a working example. – WOUNDEDStevenJones Oct 13 '22 at 16:20
  • alias your derived table. – Andrew Oct 13 '22 at 16:22
  • @WOUNDEDStevenJones your solution worked like a charm! – Adi Oct 13 '22 at 17:29

2 Answers2

1

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

DannySlor
  • 4,574
  • 1
  • 3
  • 11
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.

lemon
  • 14,875
  • 6
  • 18
  • 38