Returns NULL if Count (*) is zero
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 this?
Thank:)
source to share
First, you are missing the sentence GROUP BY
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 just want to not show rows where total_student = 0, 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 in this case.
Finally, if you instead want to replace 0 with null but still have rows, you can update the select statement to get the totals:
SELECT IF(COUNT(student_name) = 0, NULL, COUNT(student_name)) AS total_student, school_name
source to share
Try using CASE syntax: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
source to share