title: SQL group by day, show orders for each day
body: <p>I have an SQL 2005 table, let's call it Orders, in the format:</p>
  
  <pre><code>OrderID, OrderDate,  OrderAmount
  1,       25/11/2008, 10
  2,       25/11/2008, 2
  3,       30/1002008, 5
  </code></pre>
  
  <p>Then I need to produce a report table showing the ordered amount on each day in the last 7 days:</p>
  
  <pre><code>Day,        OrderCount, OrderAmount
  25/11/2008, 2,          12
  26/11/2008, 0,          0 
  27/11/2008, 0,          0 
  28/11/2008, 0,          0
  29/11/2008, 0,          0
  30/11/2008, 1,          5
  </code></pre>
  
  <p>The SQL query that would normally produce this:</p>
  
  <pre><code>select count(*), sum(OrderAmount)
      from Orders
      where OrderDate&gt;getdate()-7
      group by datepart(day,OrderDate)
  </code></pre>
  
  <p>Has a problem in that it will skip the days where there are no orders:</p>
  
  <pre><code>Day,        OrderCount, OrderAmount
  25/11/2008, 2,          12
  30/11/2008, 1,          5
  </code></pre>
  
  <p>Normally I would fix this using a tally table and outer join against rows there, but I'm really looking for a simpler or more efficient solution for this. It seems like such a common requirement for a report query that some elegant solution should be available for this already. </p>
  
  <p>So: 1. Can this result be obtain from a simple query without using tally tables?</p>
  
  <p>and 2. If no, can we create this tally table (reliably) on the fly (I can create a tally table using CTE but recursion stack limits me to 100 rows)?</p>