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

Question

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 1

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.