7

I have a table with a date column and I would like to try and group by, using a week as a time reference in order to count how many rows occured per week. I have done this for days, using GROUP BY Date(Date_Column) but i'm unsure how to do this by week?

Thanks

James
  • 71
  • 1
  • 1
  • 2
  • I had a similar question to this, but wanted to group data from multiple date columns showing the weekly total for each of those. This question here covers that - http://stackoverflow.com/questions/17563659/mysql-group-by-week-num-w-multiple-date-column – Michael K Apr 18 '17 at 14:49

5 Answers5

6
SELECT ...
FROM ....
GROUP BY YEAR(Date_column), WEEKOFYEAR(Date_Column);
CristiC
  • 22,068
  • 12
  • 57
  • 89
  • Thank you, i just found the Week() function and then read the comment above about different years. – James Jun 30 '11 at 10:47
4

Try to put a GROUP BY YEARWEEK(date_column) at the end of your query - this will take in consideration also the year the date is in.

Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
2
 SELECT WEEKOFYEAR("2017-01-01"),YEARWEEK("2017-01-01"),WEEK("2017-01-01");

Outputs:

WEEKOFYEAR("2017-01-01")    YEARWEEK("2017-01-01")  WEEK("2017-01-01")
52                          201701                  1

Looks like YEARWEEK is the best solution. No need to concat the year.

mpen
  • 272,448
  • 266
  • 850
  • 1,236
2
SELECT week(Date_Column)
FROM ....
GROUP BY week(Date_Column);
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
0
SELECT CONCAT(YEAR(Date_Column),'/',WEEK(Date_Column)) AS efdt 
FROM ....
GROUP BY efdt;
Bryan
  • 2,870
  • 24
  • 39
  • 44
AB Shaman
  • 21
  • 12