Group data on weekly (Sunday - Saturday) basis

Question

I am stuck with following problem. How can I group following data in the way that it will sum up count on weekly basis.

    +---------------------+-----+
    |          data       |count| 
    +---------------------+-----+
    | 2012-08-01 00:00:00 |  4  | 
    | 2012-08-02 00:00:00 |  5  | 
    | 2012-08-03 00:00:00 |  6  | 
    | 2012-08-04 00:00:00 |  6  | 
    | 2012-08-05 00:00:00 |  8  | 
    | 2012-08-06 00:00:00 |  6  | 
    | 2012-08-07 00:00:00 |  8  | 
    | 2012-08-08 00:00:00 |  6  | 
    | 2012-08-09 00:00:00 |  2  | 
    | 2012-08-10 00:00:00 |  0  | 
    | 2012-08-11 00:00:00 |  5  | 
    | 2012-08-12 00:00:00 |  4  | 
    | 2012-08-13 00:00:00 |  4  | 
    | 2012-08-14 00:00:00 |  0  |        
    +---------------------+-----+

The week should be calculated in the Sunday - Saturday format.....

Answer 1

Subtract the day of week from the date (assuming there's no time part) and then group by it:

select 
   dateadd(day, -(DATEPART(weekday, data) + @@DATEFIRST + 4) % 7, data)
  ,sum([count])
from tab
group by 
   dateadd(day, -(DATEPART(weekday, data) + @@DATEFIRST + 4) % 7, data)

(DATEPART(weekday, data) + @@DATEFIRST + 4) % 7 returns a number between 0 (=sunday) and 6 (saturday) independent of the @@DATEFIRST setting.

See Fiddle