MySQL count maximum number of rows
Question by Andrew Latham
I am trying to select the classes with maximum enrollment in each department from a table with the following structure:
Courses: cid, dept, name
Enrollment: cid, sid
The idea would be to count all the enrollments in each class, and find the maximum in each department. Unfortunately, I’m having trouble with the maximum-count combination.
My query was going to look something like:
SELECT c.name, MAX(COUNT(*) FROM Enrollment E WHERE E.cid = C.cid)
FROM Courses C
GROUP BY C.dept
But I can’t figure out how to get the MAX-COUNT syntax to work properly. I’ve tried a lot of different examples from Google and StackOverflow and none of them worked for me. MySQL says the syntax is wrong.
Answer by dbaseman
I like nested queries for this kind of problem. First select the enrollment counts grouped per class. Then find the max enrollment count grouped per department:
SELECT MAX(cnt) cnt, dept FROM
(
SELECT COUNT(*) cnt, dept, C.cid
FROM Courses C
INNER JOIN Enrollment E on C.cid = E.cid
GROUP BY cid, dept
) a
GROUP BY dept
Answer by Starx
There is no declaration of E, so you can’t use E.cid
So, either you do this
SELECT c.name, COUNT(c.*) as count
FROM Courses C
GROUP BY C.dept
Or,
SELECT c.name, MAX(SELECT COUNT(*) FROM Enrollment E WHERE E.cid = C.cid)
FROM Courses C
GROUP BY C.dept
