CODEWITHSUNDEEP
mysql
jack
jack

Reputation: 25

how to use avg in order by clause in sql

Hi I'm just new to sql and having some trouble with this problem. I'm sorting table subjcode by ascending order of average score and the descending order of passed-rate. and the code is 

select * 
from subjcode 
group by sno 
order by(select avg(score) from subjcode group by sno)asc ;

and it never worked it alwats says errorcode:1242.

the following table is named subjcode:

Sno     cno     score
S001    C001    78.90
S001    C002    82.90
S001    C003    59.00
S002    C001    80.90
S002    C002    72.90
S003    C001    81.90
S003    C002    81.90
S004    C001    60.90

please can someone help me?thanks

Upvotes: 1

Views: 1428

Answers (1)

Barmar
Barmar

Reputation: 781058

You don't need to put the AVG() in another SELECT. Just:

ORDER BY AVG(score)

But it doesn't make sense to use SELECT * when you're grouping. All the other columns will be selected from random rows in the group. You should just do:

SELECT sno, AVG(score) AS avg
FROM subjcode
GROUP BY sno
ORDER BY avg

Upvotes: 1

Related Questions