Group by week in SQL

Question

I have a sql query with output like this

7   2017-05-02 00:00:00.000
96  2017-05-15 00:00:00.000
79  2017-05-16 00:00:00.000
310 2017-05-17 00:00:00.000
76  2017-05-18 00:00:00.000
83  2017-05-19 00:00:00.000

I want to group them by weeks.

ideal output being

7     Week 1
644   Week 2

Any help on how to group like this will be appreciated. Thank you!

Answer 1

Another option

Example

Select SomeValue = sum(SomeValue)
      ,WeekNr=concat('Week ',dense_rank() over (Order By datepart(week,SomeDate)) )
 from YourTable
 Group By year(SomeDate),datepart(week,SomeDate)

Returns

SomeValue   WeekNr
7           Week 1
644         Week 2

Answer 2

Assuming you can live with the databases definition of a week:

select datename(year, datecol) + '-' + datename(week, datecol) as yyyyw,
       sum(col) as sumcol
from t
group by datename(year, datecol) + '-' + datename(week, datecol)
order by min(datecol);