title: Join a count query on generate_series() and retrieve Null values as '0'
body: <p>I want to count ID's per month using <code>generate_series()</code>. This query works in PostgreSQL 9.1:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM (
      SELECT  
         COUNT(mytable.id) as amount,   
         generate_series::date as serie   
         FROM mytable  
      
      RIGHT JOIN generate_series(     
         (SELECT min(date_from) FROM mytable)::date,   
         (SELECT max(date_from) FROM mytable)::date,  
         interval '1 day') ON generate_series = date(date_from)  
         WHERE version = 1   
         GROUP BY generate_series       
         ) AS foo
  GROUP BY Year   
  ORDER BY Year ASC;  
  </code></pre>
  <p>This is my output:</p>
  <pre><code>&quot;2006-12&quot; | 4  
  &quot;2007-02&quot; | 1  
  &quot;2007-03&quot; | 1  
  </code></pre>
  <p>But what I want to get is this output ('0' value in January):</p>
  <pre><code>&quot;2006-12&quot; | 4  
  &quot;2007-01&quot; | 0  
  &quot;2007-02&quot; | 1  
  &quot;2007-03&quot; | 1  
  </code></pre>
  <p>Months without <code>id</code> should be listed nevertheless.<br />
  Any ideas how to solve this?</p>
  <p>Sample data:</p>
  <pre class="lang-sql prettyprint-override"><code>drop table if exists mytable;
  create table mytable(id bigint, version smallint, date_from timestamp);
  insert into mytable(id, version, date_from) values
  (4084036, 1, '2006-12-22 22:46:35'),
  (4084938, 1, '2006-12-23 16:19:13'),
  (4084938, 2, '2006-12-23 16:20:23'),
  (4084939, 1, '2006-12-23 16:29:14'),
  (4084954, 1, '2006-12-23 16:28:28'),
  (4250653, 1, '2007-02-12 21:58:53'),
  (4250657, 1, '2007-03-12 21:58:53')
  ;
  </code></pre>