Return Count 0 with MySQL Group By

Return count 0 with mysql group by and where by date

use MONTH(t.date) = '10' AND YEAR(t.date) = '2018' condition in on cluase like below

demo

SELECT ccode.name,IFNULL(count(DISTINCT u.username),0) as total_active
,(count(DISTINCT u.username) - count(DISTINCT t.username)) as total_non_active
from m_ccode as ccode
left join m_user as u on u.ccode = ccode.id
left join t_safety_act t on t.username = u.username
and MONTH(t.date) = '10' AND YEAR(t.date) = '2018' group by ccode.id;

Include zero in COUNT with GROUP BY in MySQL

If you want all the faculties; your starting table for the JOIN should be the faculty table. Then do Left joins on the other table accordingly.

Use the following query:

SELECT `f`.`id`, `f`.`name`, COUNT(`s`.`id`) AS `total`
FROM `faculty` AS `f` 
LEFT JOIN `course` AS `c` ON `f`.`id` = `c`.`faculty_id` 
LEFT JOIN `student` AS `s` ON `c`.`id` = `s`.`course_id` 
GROUP BY `f`.`id`, `f`.`name` 
ORDER BY `f`.`name`

Show count 0 when condition is not met in MySQL group by

You can use SUM + IF to have this result:

SELECT `setCode`, SUM(if (`answer`='X', 1,0)) as answer 
FROM `mcq_answer`
WHERE examid=10
GROUP BY `setcode`

Displaying rows with count 0 with mysql group by

Classic case for a LEFT JOIN:

SELECT
  c.cname,
  COUNT(w.ename) wcount 
FROM
  company c
  LEFT JOIN works w ON c.cname = w.cname
GROUP BY
  c.cname

---

Related Topics