SELECT inside Aggregate Function

Let's say I have 3 tables like this:

class
id
capacity

student
id

student_class
id_student
id_class

SELECT c.id
FROM student_class sc
JOIN class c
  ON c.id=sc.id_class
JOIN student s
  ON s.id=sc.id_student
HAVING MAX((SELECT(COUNT(sc2.id) FROM student_class sc2 WHERE sc2.id_student=s.id AND sc2.id_course=c.id)/c.capacity))

I want to find the course with the highest enrolled/capacity ratio.

Should I write the having clause like this using a select clause inside an aggregate function?

HAVING MAX((query that gives me the total number of students enrolled in a course)/course capacity))

Or is there a better way to do it?

Solution

I want to find the course with the highest enrolled/capacity ratio.

You would use aggregation to calculate the ratio. Then you can use ORDER BY and LIMIT to return one value. Note that this returns one row, even if there are ties:

SELECT c.id, COUNT(*) / c.capacity as ratio
FROM student_class sc JOIN
     class c
     ON c.id = sc.id_class
GROUP BY c.id, c.capacity
ORDER BY ratio DESC
LIMIT 1;