Q:

Count of the Course Name

i am struggling with one logic in query
i have two tables called "students" and "courses" here i need the count of the students in particular course
 students table
 student_id | course_id 
   1           11
   2           11
   3           11
   4           12
   5           12
   6           13
   7           18

courses table
course_id | course_name
   11          test
   12          test
   13          xyz
   18          xyz

Here what is my requirement is i want the how students are there in same course name of the students irrespective of the course_id
output i need is

course_name | student_count
 test              5
 xyz               2

Thanks in advance    

A:

select c.course_name, count(s.student_id) as student_count
from courses c
left join students s on s.course_id = c.course_id
group by c.course_name

SQLFiddle demo

A:

SELECT c.`course_name`, count(s.`student_id`) AS student_count FROM course c, students s WHERE s.`course_id` = c.`course_id` GROUP BY c.`course_name`;

