MySQL: List students (Sname) who enrolled in more than 6 (distinct) classes in a term.

Question

Given the schema:

Student(Snum, Sname)
Course(Cnum, Cname)
Prerequisite(Cnum, Prereq)
Professor(Pnum,Pname, Dept, Office)
Enrollment(Snum, Cnum, Term, Section, Mark)
Schedule(Cnum, Term, Section, Days, Time, Room)
Class(Cnum, Term, Section, Instructor)

I have come up with:

SELECT * FROM Student s
HAVING MIN(
    SELECT COUNT(*) FROM Enrollment e
    WHERE e.Snum = s.Snum
    GROUP BY e.Term
) > 6

But I am getting:

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM Enrollment e WHERE e.Snum = s.Snum GROUP BY e.Term ) >' at line 3 */

Any idea as to how I can fix this? Thanks!

Also, it would be reassuring to know that I have the right logic =)

EDIT: FINAL ANSWER...

SELECT Student.Sname
FROM(
    SELECT COUNT(DISTINCT Cnum) as `classes`, e.Term as `term`, e.Snum as `student`
    FROM Enrollment e
    GROUP BY e.Term, e.Snum
) x
JOIN Student ON Student.Snum = `student`
WHERE x.`classes` > 6

Answer 1

SELECT * FROM Student s
WHERE (
    SELECT COUNT(*) FROM Enrollment e
    WHERE e.Snum = s.Snum
    AND e.Term = <some term>
) > 6

No need to use an aggregate function here, so use a normal WHERE (not HAVING) for the subquery as well.

Edit: modified to only check one term at a time. If you absolutely need to check all terms, I don't immediately have a good idea.

Answer 2

If you want to see students that had at least 6 classes in each term:

SELECT * FROM Student s
WHERE (SELECT MIN(perTerm) FROM(
    SELECT COUNT(DISTINCT Cnum) as perTerm FROM Enrollment e
    WHERE e.Snum = s.Snum
    GROUP BY e.Term
)) > 6

Here you go. You were almost there, but HAVING cannot be used if you don't have a GROUP BY.

If you want any term replace MIN with MAX and if you want a specific term use the updated version of Vegard.

---

If the double subquery doesn't work try this one:

SELECT `student`
FROM(
    SELECT COUNT(DISTINCT Cnum) as `classes`, s.Term as `term`, s.Snum as `student`
    FROM Enrollment e
    GROUP BY e.Term, e.Snum
)
GROUP BY `term`
HAVING MIN(`classes`) > 6