Question 181453 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql count with GROUP BY returns 1 even when count is 0

I have two tables in MySql i.e., subjects and photos and I wished to count the number of photos on each subjects

SELECT a.id, a.name, count(a.id) as `refcount`, 
FROM `subjects` a 
LEFT JOIN `photos` b ON (a.id = b.subject_id)
GROUP by a.id
ORDER BY a.name";

returns 1 even when the rowcount()=0. How to fix it

I tried various MySql syntax including count(field), but in vain

Tags:

2

Answers


  1. 0

    You will need to count photos.id (b.id), if no photos are found for the given subject, the query will return null, count(null) = 0.

    SELECT a.id, a.name, count(b.id) as `refcount`
FROM `subjects` a 
LEFT JOIN `photos` b ON a.id = b.subject_id
GROUP by a.id, a.name
ORDER BY a.name;
    Login or Signup to reply.
  2. 0

    You can use this: SELECT a.id, a.name, SUM(IF(b.id IS NULL, 0, 1)) as refcount, FROM subjects a LEFT JOIN photos b ON (a.id = b.subject_id) GROUP by a.id order by a.name.

    PS: I didn’t try it

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search