Menu
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
688
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 .
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.
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.
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.
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 |
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
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
