MySQL count in same table including zero count values

Question

I have this table structure with data:

INSERT INTO `test` (`id`, `email`, `id_user_ref`, `name`) VALUES
(1, '[email protected]', NULL, 'Mike'),
(2, '[email protected]', '1', 'Jhonny'),
(3, '[email protected]', '1', 'Michael'),
(4, '[email protected]', '2', 'Jorhe'),
(5, '[email protected]', '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:

you want to count how many users mike invited? then you will run into a inner join from the table to itself :-) — Najzero
– Najzero, Commented Apr 2, 2013 at 7:13
i need to know how for every person how many persons have their id ref including 0 — Dsp Marian
– Dsp Marian, Commented Apr 2, 2013 at 7:17

Mahmoud Gamal · Accepted Answer · 2013-04-02 07:18:35Z

23

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 |

answered Apr 2, 2013 at 7:18

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

C4d Over a year ago

ON t1.id = t2.id_user_ref doesnt makes any sense to me. These columns arnt the same!?

Mahmoud Gamal Over a year ago

@C4ud3x - The column id is the id of the user, the column id_user_ref is a reference id to the id column, the user is looking for a way to get the count of the referenced ids by each id with 0 for those who don't have a reference. So the way to do this is to LEFT JOIN the same table, like what I did in my query.

Irfandi D. Vendy Over a year ago

This one works for me, even if I've no idea what's going on.... I think I need to learn more about LEFT JOIN...

Biswanath · Accepted Answer · 2013-04-02 07:30:39Z

1

Can you try this ?

SELECT a.id,
CASE  WHEN b.refNr IS NULL THEN 0
ELSE b.refNr END FROM test a LEFT JOIN
( SELECT id_user_ref, COUNT(name) AS refNr
    FROM test
    WHERE id_user_ref IS NOT NULL
    GROUP BY id_user_ref) b
ON a.id = b.id_user_ref

Sql Demo

answered Apr 2, 2013 at 7:30

Biswanath

9,20512 gold badges48 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySQL count in same table including zero count values

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related