-1

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.....

chinnusaccount
  • 239
  • 6
  • 20
  • Check related questions first. I.e. [this](http://stackoverflow.com/questions/9182644/query-a-mysql-database-and-group-by-date-range-to-create-a-chart?rq=1) and [this](http://stackoverflow.com/questions/10597101/weekly-total-sums?rq=1) – Pilskalns Jun 15 '15 at 13:33
  • What database are you using? I am guessing SQL Server from the "mssql-jdbc", but you should explicitly tag the question. – Gordon Linoff Jun 15 '15 at 13:35
  • I tried following query...but not getting the result in Sunday - Saturday format select DATEADD(week, DATEDIFF(week,0,data), 0) as Monday, sum(count) from table where data>getdate()-30 group by DATEADD(week, DATEDIFF(week,0,data), 0); – chinnusaccount Jun 15 '15 at 13:40

1 Answers1

3

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

dnoeth
  • 59,503
  • 4
  • 39
  • 56