CODEWITHSUNDEEP
mysqlsql
James
James

Reputation: 71

MySQL grouping by week, based on a date column?

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

Upvotes: 7

Views: 26801

Answers (5)

mpen
mpen

Reputation: 282825

 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.

Upvotes: 2

AB Shaman
AB Shaman

Reputation: 21

SELECT CONCAT(YEAR(Date_Column),'/',WEEK(Date_Column)) AS efdt 
FROM ....
GROUP BY efdt;

Upvotes: 0

CristiC
CristiC

Reputation: 22698

SELECT ...
FROM ....
GROUP BY YEAR(Date_column), WEEKOFYEAR(Date_Column);

Upvotes: 6

Tudor Constantin
Tudor Constantin

Reputation: 26861

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.

Upvotes: 4

Jonathan Hall
Jonathan Hall

Reputation: 79546

SELECT week(Date_Column)
FROM ....
GROUP BY week(Date_Column);

Upvotes: 2

Related Questions