title: SQL query with join, sum, group by, etc
body: <p>I'm trying to build a report that will look like this:</p>
  
  <pre><code>      jan feb mar apr may jun jul ago sep oct nov dec
  food   0   1   1   2   0   0   3   1   0   0   1   1
  car    1   0   0   0   1   2   1   0   1   2   3   4
  home   0   0   1   2   2   2   5   1   2   4   0   0
  other  0   0   0   0   0   0   0   0   0   0   0   0
  </code></pre>
  
  <p>I have two tables: <code>t_item</code> and <code>t_value</code>. <code>t_item</code> has 2 columns: <code>itemID</code> and <code>itemName</code>. <code>t_value</code> has 3 columns: <code>itemID</code>, <code>value</code>, <code>date</code>.</p>
  
  <p>With the following query I can generate a list with all the itens, even with the empty ones.</p>
  
  <pre><code>SELECT t_item.itemID, ISNULL(SUM(t_value.value), 0) AS value
  FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
  GROUP BY t_item.itemID
  </code></pre>
  
  <p>But, if I try to include a MONTH column (as follows) the result will show only the items with values...</p>
  
  <pre><code>SELECT t_item.itemID, ISNULL(SUM(t_value.value), 0) AS value, MONTH(date) AS date
  FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
  GROUP BY t_item.itemID, MONTH(date)
  </code></pre>
  
  <p>Is it possible to do it? How do I include into the results the itens with no values and group then by month?</p>
  
  <p>TIA,</p>
  
  <p>Bob</p>