Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL count in same table including zero count values

Tags:

mysql

count

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:

id  refNr
1    2
2    1
3    1
4    0
5    0
like image 688
Dsp Marian Avatar asked Apr 02 '13 07:04

Dsp Marian


People also ask

How do you include zeros in a COUNT aggregate?

A simple JOIN will not return the desired result; it will show only those buyers that have one or more service appointments. To include zeros resulting from COUNT() , you'll have to use LEFT JOIN or RIGHT JOIN .

Does COUNT in SQL COUNT 0?

The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.

Does MySQL COUNT include NULL?

Not everyone realizes this, but the COUNT function will only include the records in the count where the value of expression in COUNT(expression) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations.

How do you show months if it has no record and force it to zero if NULL on MySQL?

SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at); You can append that into your query like: SELECT IFNULL(SUM(total),0) as total_orders, mnt from (SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at)) mn LEFT JOIN orders o ON mn.


2 Answers

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 |
like image 68
Mahmoud Gamal Avatar answered Oct 24 '22 07:10

Mahmoud Gamal


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

like image 27
Biswanath Avatar answered Oct 24 '22 07:10

Biswanath