denoise denoise - 8 days ago 7
MySQL Question

COUNT(*) with LEFT JOIN and GROUP BY to include NULL in MySQL

I'm trying to get the results from a table including the appearance count in a foreign table. This table can have 0 or more appearances.

like in the following example:

table: color

+------+---------+
| id | name |
+------+---------+
| 1 | red |
| 2 | blue |
| 3 | yellow |
| 4 | green |
+------+---------+


table: fruit

+--------+----------+
| name | color_id |
+--------+----------+
| apple | 1 |
| banana | 3 |
| grape | 4 |
| lemon | 3 |
+--------+----------+


So I need to list every color and the occurrence in the fruit table, returning something like this:

1, red, 1
2, blue, 0
3, yellow, 2
4, green, 1


I'm trying with this query:

SELECT `c`.`id`, `c`.`name`, COUNT(1)
FROM color `c`
LEFT JOIN fruit `f`
ON `c`.`id` = `f`.`color_id`
GROUP BY `c`.`id`


This query is returning a count of 1 for "blue" instead of 0. beacuse the color "blue" doesn't appear in the fruit table

Answer

This works:

SELECT c.id, COUNT(f.name)
FROM color c
LEFT JOIN fruit f ON c.id = f.color_id
GROUP BY c.id

You have to count a field of fruit, so that NULL can be returned, which becomes a zero.