CODEWITHSUNDEEP
mysqljoingroup-by
denoise
denoise

Reputation: 1097

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

Upvotes: 8

Views: 20137

Answers (2)

Cristina Carrasco
Cristina Carrasco

Reputation: 713

You put count(1), change count(f.color_id) 

select  
    c.id
    , c.name
    , count(f.color_id) as [Color Count]
from color as c
    left join fruit as f
    on c.id = f.color_id
group by c.id, c.name

Upvotes: 3

fafl
fafl

Reputation: 7385

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.

Upvotes: 21

Related Questions