Return NULL if Count(*) is zero

Question

I have the following mysql query:

SELECT count(student_name) AS total_student,school_name FROM `student` 
LEFT JOIN school_info ON school_info.school_id=student.school_id
WHERE student.status='0'

It Returns:

total_student   school_name
  0               NULL

What I am trying to achieve is, if total_student = 0 then show no value or NULL

total_student   school_name 

Could you please tell me how to do it?

Thanks :)

Answer 1

First, you're missing a GROUP BY clause at the bottom of your query to group by school_name:

SELECT count(student_name) AS total_student, school_name
FROM student
    LEFT JOIN school_info ON school_info.school_id = student.school_id
WHERE student.status = '0'
GROUP BY school_name

Then, if you want to simply not show rows where total_student = 0 then you can use the MySQL HAVING clause:

SELECT count(student_name) AS total_student, school_name
FROM student
    LEFT JOIN school_info ON school_info.school_id = student.school_id
WHERE student.status = '0'
GROUP BY school_name
HAVING count(student_name) > 0

Or, you can change LEFT JOIN to INNER JOIN to accomplish the same thing in this case.

Finally, if instead you want to replace 0 with null but still have rows, you could update the select statement getting the totals to:

SELECT IF(COUNT(student_name) = 0, NULL, COUNT(student_name)) AS total_student, school_name

Answer 2

Add a HAVING clause to filter out the 0 rows:

SELECT count(student_name) AS total_student,school_name FROM `student` 
LEFT JOIN school_info ON school_info.school_id=student.school_id
WHERE student.status='0'
HAVING total_student > 0