need to select top students from college by average mark on a subject. (there are 2 tables: students: id, name, subjects: id, name, marks: : id, student_id, subject_id, val)
The query I made:
select s.id, sb.id, avg(m.val) as avgmark
from marks m
join students s on s.id = m.student_id
join subjects sb on sb.id = m.subject_id
where sb.name = 'math' and avgmark > 80
GROUP BY student_id, subject_id
ORDER BY avgmark DESC
doesn't work AFAIK because of aggregate functions work after where clause works.
What is the reasonable way to perfom it?
Do I need to use having somehow?
Does ORDER BY work with aggregate functions?
This is not about ORDER BY. You aggregate the joined rows in order to end up with one row per student and subject ID. WHERE happens before aggregation. In WHERE we look at the original rows, not at aggregation results. If you want to look at an aggregation result like the average, then you must place the condition in the HAVING clause instead.
This query gets you all students with an average grade over 80, showing the top students first:
select m.student_id, m.subject_id, avg(m.val) as avgmark
from marks m
join students s on s.id = m.student_id
join subjects sb on sb.id = m.subject_id
where sb.name = 'math'
group by m.student_id, m.subject_id
having avg(m.val) > 80
order by avgmark desc;
The students table is superflouous by the way and the subjetcs table only needed for a lookup, so I'd write:
select m.student_id, m.subject_id, avg(m.val) as avgmark
from marks m
where m.subject_id = (select sb.id from subjects sb where sb.name = 'math')
group by m.student_id, m.subject_id
having avg(m.val) > 80
order by avgmark desc;