SQL How To Group by Date Ranges

Can someone help me figure out to group by a range of dates??

Right now I have query similar to this

    Select date, count(x)
    from data
    group by date

This returns results that look like this

    2011/1/1   10
开发者_如何学编程    2011/1/2   5
    2011/1/3   8
    2011/1/4   3

etc...

But I would like to count every 2 days so that the data would look like this

    2011/1/1   15
    2011/1/3   11

Any ideas??

Thanks

You could normalize the dates into groups of 2 by converting to a numerical integer value, and reducing to the even numbers. A simple way to do that is val / 2 * 2, because the first / 2 will be truncated of any decimal places (as long as the type of val is an integer!), and * 2 will return it to the original value except normalized to an even number. Here is an example that normalizes and groups the results using a CTE data source:

;with Data as ( 
    select '1/1/2011' as [date], 1 as x union
    select '1/1/2011' as [date], 2 as x union
    select '1/1/2011' as [date], 3 as x union
    select '1/1/2011' as [date], 4 as x union
    select '1/1/2011' as [date], 5 as x union
    select '1/1/2011' as [date], 6 as x union
    select '1/1/2011' as [date], 7 as x union
    select '1/1/2011' as [date], 8 as x union
    select '1/1/2011' as [date], 9 as x union
    select '1/1/2011' as [date], 10 as x union
    select '1/2/2011' as [date], 11 as x union
    select '1/2/2011' as [date], 12 as x union
    select '1/2/2011' as [date], 13 as x union
    select '1/2/2011' as [date], 14 as x union
    select '1/2/2011' as [date], 15 as x union
    select '1/3/2011' as [date], 16 as x union
    select '1/3/2011' as [date], 17 as x union
    select '1/3/2011' as [date], 18 as x union
    select '1/3/2011' as [date], 19 as x union
    select '1/3/2011' as [date], 20 as x union
    select '1/3/2011' as [date], 21 as x union
    select '1/3/2011' as [date], 22 as x union
    select '1/3/2011' as [date], 23 as x union
    select '1/4/2011' as [date], 24 as x union
    select '1/4/2011' as [date], 25 as x union
    select '1/4/2011' as [date], 26 as x
)
Select
    cast(cast(cast(Date as datetime) as integer) / 2 * 2 as datetime) as date,
    count(x)
from data
group by cast(cast(Date as datetime) as integer) / 2 * 2

Output:

date                    (No column name)
2011-01-01 00:00:00.000 15
2011-01-03 00:00:00.000 11

Select floor((date - trunc(date,'MM')) / 2), count(x)
  from data
 group by floor((date - trunc(date,'MM')) / 2)