CODEWITHSUNDEEP
sqlmysql
matthy
matthy

Reputation: 8334

MYSQL get amount of results

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?

Upvotes: 0

Views: 141

Answers (3)

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

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

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332551

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

Upvotes: 1

gahooa
gahooa

Reputation: 137282

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

Upvotes: 1

Related Questions