8

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

denoise
  • 1,067
  • 2
  • 14
  • 40
  • 1
    doesn't COUNT(1) always return 1? – Jerry Dec 01 '16 at 19:18
  • 1
    Well, you are counting the number of 1's, and mysql does that. Replace `count(1)` with `count(f.name)`, then it will count the number of fruits. – Solarflare Dec 01 '16 at 19:18
  • @Jerry No, `COUNT(anything)` counts the number of non-null values encountered. – Uueerdo Dec 01 '16 at 19:31
  • and there is exactly one non-null value in `count(1)` - the number 1. You can test that with the simple query `SELECT COUNT(1)`; `SELECT COUNT(2)` also returns 1, because there is still just the one scalar value in the `COUNT` expression. – Jerry Dec 01 '16 at 19:35
  • no, COUNT(*) is the same as COUNT(1): http://stackoverflow.com/questions/1221559/count-vs-count1 – denoise Dec 01 '16 at 19:39
  • 1
    @Jerry, yes, no matter what you count with a bare(FROM-less) SELECT (other than a NULL literal, or null-valued variable), the result will always be 1. If you `SELECT COUNT([any non-null constant or unchanging variable]) FROM [an N rowed table]` you will get N. If you `SELECT COUNT(fieldX) FROM tableY` and half of the rows in tableY have a null value for fieldX, you will get a number equal to half the rows in tableY. `COUNT(1)` being equal to 1 has nothing to do with a scalar value in a count expression; `COUNT(DISTINCT [non-null constant...])` will always be one though. – Uueerdo Dec 05 '16 at 19:58

2 Answers2

21

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.

fafl
  • 7,222
  • 3
  • 27
  • 50
3

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
Cristina Carrasco
  • 703
  • 6
  • 20