title: SQL Server : calculate monthly total sales incl empty months
body: <p>I'm trying to calculate the total sales of a product in a month, but I would like it to include any "empty" months (with no sales) and only select the latest 12 months.</p>
  
  <p>This is my code so far.</p>
  
  <pre><code>declare
  @ProductNo int
  
  set @ProductNo = 1234
  
  SELECT 
  YEAR(o.OrderDate) as 'Year', MONTH(o.OrderDate) as 'Month', sum(Amount) as 'Units sold',[ProductNo]
  
    FROM [OrderLine] ol
    inner join [Order] o on ol.OrderNo = o.OrderNo
    where ProductNo = @ProductNo
  
    Group by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
    Order by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
  </code></pre>
  
  <p>This returns</p>
  
  <pre><code>Year    Month   Units sold
  2011    6       2
  2011    10      1
  2011    11      1
  2012    2       1
  </code></pre>
  
  <p>But I would like it to return.</p>
  
  <pre><code>Year    Month   Units sold
  2011    4       0
  2011    5       0
  2011    6       2
  2011    7       0
  2011    8       0    
  2011    9       0
  2011    10      1
  2011    11      1
  2011    12      0
  2012    1       0
  2012    2       2
  2012    3       0
  </code></pre>
  
  <p>I'm using SQL Server 2008 R2 Sp1</p>