title: How do I fetch data grouped by month and year including the period with no data?
body: <p>Relatively new to SQL and I am stumped on this little issue. This doesn't seem to be very difficult to do, but I just can't seem to figure it out.</p>
  
  <p>I am trying to get a count of transactions from a table, but I can't seem to get sql to get me to show all of the months instead of only the months and the year that the transactions occured in. </p>
  
  <p>Here is the query:</p>
  
  <pre><code>SELECT      YEAR(dbo.countproject.trans_date)   AS [TransYear]
          ,   MONTH (dbo.countproject.trans_date) AS [TransMonth]
          ,   COUNT(Id)                           AS TransNum 
  FROM        dbo.countproject
  WHERE       dbo.countproject.make_name  = 'Honda'
  AND         dbo.countproject.model_name = 'Civic'
  AND         dbo.countproject.type       = 'Sale'
  AND         dbo.countproject.trans_type LIKE '%%EU'
  AND         dbo.countproject.mfr        = '2000'
  GROUP BY    YEAR(dbo.countproject.trans_date)
          ,   MONTH(dbo.countproject.trans_date)
  ORDER BY    YEAR(dbo.countproject.trans_date)
  </code></pre>
  
  <p>The query returns the following result set:</p>
  
  <pre><code>| TransYear | TransMonth | TransNum |
  |-----------|------------|----------|
  |  2004     |     1      |     5    |
  |  2004     |     3      |     1    |
  |  2005     |     4      |     2    |
  </code></pre>
  
  <p>and so forth.... </p>
  
  <p>I am trying to get it to show all the months and years even if the value is NULL. </p>
  
  <p>I tried creating a new table which will have the year and the month as columns to get it to join somehow, but I am lost.</p>
  
  <p>Any help would be appreciated. </p>