0

How can I get the number of records over a date interval, including dates with no records?

For example I have the following table:

   DATE    | INSERTID  
2011-12-10 |    1  
2011-12-10 |    2  
2011-12-12 |    3  
2011-12-13 |    4  
2011-12-15 |    5  
2011-12-15 |    6

and the result to be:

   DATE    | COUNT(INSERTID)  
2011-12-10 |   2  
2011-12-11 |   0  
2011-12-12 |   1  
2011-12-13 |   1  
2011-12-14 |   0  
2011-12-15 |   2
CristiC
  • 22,068
  • 12
  • 57
  • 89
  • possible duplicate of [Selecting count of zero while grouping by column](http://stackoverflow.com/questions/3824686/selecting-count-of-zero-while-grouping-by-column) – Joe Stefanelli Dec 15 '11 at 20:28

1 Answers1

1

I believe you're going to want to start by generating your list of days, then left join to your object table, grouping on date and doing a count() on the object table. There's already an answer that covers generating the dates, from the looks of it.

Community
  • 1
  • 1
D. Lambert
  • 1,304
  • 6
  • 12