title: LEFT JOIN to same table
body: <p>I want to derive monthly subtotals for client accounts and if the client has no activity for the month, it will return 0.  There is a billing_month field in the table which contains every month; even if the customer didn't have activity, another customer would.  I wanted to do a left join on the table so that every billing_month would show up but that's not working.  What am I doing wrong here?</p>
  
  <pre><code>SELECT COALESCE (SUM(b.Adjusted_Price), 0) AS Amount, a.Billing_Month,    
  dbo.tblSubaccounts.Master_Account_Name
  FROM dbo.tblBilling_Detail AS a 
  LEFT OUTER JOIN dbo.tblBilling_Detail AS b ON a.ID = b.ID 
  INNER JOIN dbo.tblSubaccounts ON 
  dbo.tblSubaccounts.ID = b.Linked_Account
  GROUP BY a.Billing_Month, dbo.tblSubaccounts.Master_Account_Name
  </code></pre>
  
  <p>Sample Data - Note that customer 2 (the customer ID) has no data for May so I would want May to return 0.  </p>
  
  <pre><code>ID  Billing_Month   Adjusted_Price  Linked_Account
  1      3/1/14        100            2
  2      4/1/14        200            2
  3      5/1/14        300            3
  </code></pre>