Dsp Marian Dsp Marian - 6 months ago 25
MySQL Question

MySQL count in same table including zero count values

I have this table structure with data:

INSERT INTO `test` (`id`, `email`, `id_user_ref`, `name`) VALUES
(1, 'email@gmail.com', NULL, 'Mike'),
(2, 'email2@gmail.com', '1', 'Jhonny'),
(3, 'email3@gmail.com', '1', 'Michael'),
(4, 'email4@gmail.com', '2', 'Jorhe'),
(5, 'email5@gmail.com', '3', 'Mia');


I need to count the id_user_ref for all users with this query:

SELECT id, COUNT(name) AS refNr FROM test GROUP BY id_user_ref
HAVING id_user_ref IS NOT NULL;


This works but the problem is that i need to display all results even if the count result is 0.

I tried several left joins with the same table but without any success.

The output should be:

id refNr
1 2
2 1
3 1
4 0
5 0

Answer

Try this:

SELECT 
  t1.id, 
  IFNULL(COUNT(t2.name), 0) AS refNr 
FROM test AS t1
LEFT JOIN test AS t2 ON t1.id = t2.id_user_ref
GROUP BY t1.id;

SQL Fiddle DEmo

This will give you:

| ID | REFNR |
--------------
|  1 |     2 |
|  2 |     1 |
|  3 |     1 |
|  4 |     0 |
|  5 |     0 |