CODEWITHSUNDEEP
mysqlsqldatabase
Andrew Latham
Andrew Latham

Reputation: 6132

MySQL count maximum number of rows

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.

Upvotes: 0

Views: 3468

Answers (3)

dotoree
dotoree

Reputation: 2993

SELECT MAX(cnt) FROM (
    SELECT c.name, COUNT(E.*) AS cnt FROM Enrollment E
    INNER JOIN Courses C ON E.cid = C.cid
    GROUP BY C.dept
) final

Upvotes: 0

McGarnagle
McGarnagle

Reputation: 102753

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

Upvotes: 2

Starx
Starx

Reputation: 78981

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

Upvotes: 0

Related Questions