MYSQL get amount of results...

Question

When you use mysql, you can do something like this:

SELECT * FROM student WHERE teacher_id = 1

...then you get the amount of results, and with that the result. But I only want the amount of results but then for every teacher.

Is that possible in mysql like sizeof or something?

Also, if there are teachers that have no students is it true that there not in the list? or does it have to say 0?

Answer 1

Do you mean you want the number of students for every teacher? You can use something like this:

SELECT teacher_id, count(*) AS student_count
FROM student
GROUP BY teacher_id

This will not include teachers that don't have any students. To include them in the results, you need to use a JOIN (assuming you have a teacher table):

SELECT teacher.id, sum(if(student.id IS NULL, 0, 1)) AS student_count
FROM teacher
    LEFT JOIN student ON student.teacher_id=teacher.id
GROUP BY teacher.id

Answer 2

SELECT teacher_id, count(*) as count FROM student GROUP BY teacher_id;

Answer 3

Use:

   SELECT t.teacher_id,
          IFNULL(ns.num_students, 0)
     FROM TEACHERS t
LEFT JOIN (SELECT s.teacher_id,
                  COUNT(s.student_id) 'num_students'
             FROM STUDENTS s
         GROUP BY s.teacher_id) ns ON ns.teacher_id = t.teacher_id