Listing courses having atleast 1 student and having maximum no. of students

I just read the SitePoint tutorial on SQL joins. It was absolutely fantastic, I must say!!
However, while practicing the queries explained in the tutorial, I tried to have some fun with the tables.

I want to list the courses which have atleast 1 students enrolled with them, and here is my query-

select course.name, count(user.name) from course left join user on user.course = course.id group by course.id where count(user.name) > 0;

But this does not work.

Also, I would like to list the name of course and no. of students in it, that has the maximum no. of students enrolled. I can’t figure out any query for that.

Can anybody please help me with that?

[quote=“prateekmathur1991, post:1, topic:193800, full:true”]But this does not work.[/quote]you’ll get a better response if you tell us what the error message is

@r937, Here is the error I got. Hope this helps-

ERROR 1064 (42000): 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 'where
 count(user.name) > 0' at line 1

use HAVING instead of WHERE

:smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.