How Can I Group Data by Week in MySQL?

Grouping by Week in MySQL: YEARWEEK Function

MySQL offers a YEARWEEK function that can be used to group data by week. This function returns a string representing the year and week number for a given date. For example:

SELECT YEARWEEK('2023-03-08')

returns '202309'. This indicates that March 8, 2023, falls in the 9th week of 2023.

The YEARWEEK function includes an optional second argument that specifies the week start mode. The default is 1, which indicates that the week starts on Sunday. A mode of 0 indicates that the week starts on Monday, and a mode of 2 indicates that the week starts on Saturday.

To group data by week using the YEARWEEK function:

SELECT YEARWEEK(date, 0), COUNT(*) FROM table
GROUP BY YEARWEEK(date, 0)

This query groups the rows in the 'table' by the week of the year, assuming weeks start on Monday. The 'COUNT(*)' function counts the number of rows in each week.

Combining YEAR and WEEK Functions

An alternative approach to grouping by week is to use a combination of the YEAR and WEEK functions. This can be useful if you want more control over the grouping logic. For example, the following query groups data by the year and week number, regardless of the week start mode:

SELECT YEAR(date), WEEK(date), COUNT(*) FROM table
GROUP BY YEAR(date), WEEK(date)

EDIT:

As noted by Martin in the comments, you can also use the following formula to group by week:

CONCAT(YEAR(date), '/', WEEK(date))

This approach may be more eye-pleasing than using the YEARWEEK function.

The above is the detailed content of How Can I Group Data by Week in MySQL?. For more information, please follow other related articles on the PHP Chinese website!