title: How to return empty groups in SQL GROUP BY clause
body: <p>I have a query to return how much is spent on-contract and off-contract at each location, that returns something like this:</p>
  
  <pre><code>Location     | ContractStatus | Expenses
  -------------+----------------+---------
  New York     | Ad-hoc         | 2043.47
  New York     | Contracted     | 2894.57
  Philadelphia | Ad-hoc         | 3922.53
  Seattle      | Contracted     | 2522.00
  </code></pre>
  
  <p>The problem is, I only get one row for locations that are all ad-hoc or all contracted expenses. I'd like to get two rows back for each location, like this:</p>
  
  <pre><code>Location     | ContractStatus | Expenses
  -------------+----------------+---------
  New York     | Ad-hoc         | 2043.47
  New York     | Contracted     | 2894.57
  Philadelphia | Ad-hoc         | 3922.53
  Philadelphia | Contracted     |    0.00
  Seattle      | Ad-hoc         |    0.00
  Seattle      | Contracted     | 2522.00
  </code></pre>
  
  <p>Is there any way I can accomplish this through SQL? Here is the actual query I'm using (SQL Server 2005):</p>
  
  <pre><code>SELECT Location, 
       CASE WHEN Orders.Contract_ID IS NULL 
            THEN 'Ad-hoc' ELSE 'Contracted' END 
                       AS ContractStatus,
       SUM(OrderTotal) AS Expenses
  FROM Orders
  GROUP BY Location, 
      CASE WHEN Orders.Contract_ID IS NULL 
           THEN 'Ad-hoc' ELSE 'Contracted' END
  ORDER BY Location ASC, ContractStatus ASC
  </code></pre>