CODEWITHSUNDEEP
mysql
John Smith
John Smith

Reputation: 6207

How to order by a "calculated" (not MySQL function) value?

there are students and their marks:

Student:
ID NAME
1  a
2  b
3  c
4  d

Mark:
ID STUDENT_ID MARK
1  1          4
1  1          2
1  3          4

I would like to get an ordered list about their average marks, to get:

a, (4+2)/2
b, -,
c, 4/1
d, -

I must not calculate it while SELECT-ing, not even store it in a column in Students table. The interesting part is when I would want to order and limit (paging) the result. To have a correct ordering, I must know all averages - so I wont use SELECT query without LIMIT. But how to? Ideally, ordering by ID or NAME is easy.

Upvotes: 0

Views: 26

Answers (2)

Koray Küpe
Koray Küpe

Reputation: 736

You can get users with their average marks with this query:

SELECT student.*, AVG(mark.mark) as average_mark
FROM users
LEFT OUTER JOIN mark m on m.student_id= users.id
GROUP BY users.id
ORDER BY average_mark DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can order by an expression, including a subquery:

order by (select avg(mark) from marks m where m.student_id = s.id)

I'm not sure I would want to do this as part of paging, unless the underlying data is pretty small.

Upvotes: 1

Related Questions