title: COUNT(*) with LEFT JOIN and GROUP BY to include NULL in MySQL
body: <p>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.</p>
  
  <p>like in the following example:</p>
  
  <p>table: color</p>
  
  <pre><code>+------+---------+
  |  id  |   name  |
  +------+---------+
  |  1   |   red   |
  |  2   |   blue  |
  |  3   |  yellow |
  |  4   |  green  |
  +------+---------+
  </code></pre>
  
  <p>table: fruit</p>
  
  <pre><code>+--------+----------+
  |  name  | color_id |
  +--------+----------+
  | apple  |    1     |
  | banana |    3     |
  | grape  |    4     |
  | lemon  |    3     |
  +--------+----------+
  </code></pre>
  
  <p>So I need to list every color and the occurrence in the fruit table, returning something like this:</p>
  
  <pre><code>1, red, 1
  2, blue, 0
  3, yellow, 2
  4, green, 1
  </code></pre>
  
  <p>I'm trying with this query:</p>
  
  <pre><code>SELECT `c`.`id`, `c`.`name`, COUNT(1)
  FROM color `c`
  LEFT JOIN fruit `f`
  ON `c`.`id` = `f`.`color_id`
  GROUP BY `c`.`id`
  </code></pre>
  
  <p>This query is returning a count of <strong>1</strong> for "blue" instead of <strong>0</strong>. beacuse the color "blue" doesn't appear in the fruit table</p>