title: MySQL: List students (Sname) who enrolled in more than 6 (distinct) classes in a term
body: <p>Given the schema:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>I have come up with:</p>
  
  <pre><code>SELECT * FROM Student s
  HAVING MIN(
      SELECT COUNT(*) FROM Enrollment e
      WHERE e.Snum = s.Snum
      GROUP BY e.Term
  ) &gt; 6
  </code></pre>
  
  <p>But I am getting:</p>
  
  <blockquote>
    <p>/* 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 */</p>
  </blockquote>
  
  <p>Any idea as to how I can fix this? Thanks!</p>
  
  <p>Also, it would be reassuring to know that I have the right logic =)</p>
  
  <p>EDIT: FINAL ANSWER...</p>
  
  <pre><code>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` &gt; 6
  </code></pre>