How to group by week in MySQL?

by

You can use both YEAR(timestamp) and WEEK(timestamp), and use both of the these expressions in the SELECT and the GROUP BY clause.

Not overly elegant, but functional…

And of course you can combine these two date parts in a single expression as well, i.e. something like

SELECT CONCAT(YEAR(timestamp), "https://stackoverflow.com/", WEEK(timestamp)), etc...
FROM ...
WHERE ..
GROUP BY CONCAT(YEAR(timestamp), "https://stackoverflow.com/", WEEK(timestamp))

Edit: As Martin points out you can also use the YEARWEEK(mysqldatefield) function, although its output is not as eye friendly as the longer formula above.

Edit 2 [3 1/2 years later!]:
YEARWEEK(mysqldatefield) with the optional second argument (mode) set to either 0 or 2 is probably the best way to aggregate by complete weeks (i.e. including for weeks which straddle over January 1st), if that is what is desired. The YEAR() / WEEK() approach initially proposed in this answer has the effect of splitting the aggregated data for such “straddling” weeks in two: one with the former year, one with the new year.
A clean-cut every year, at the cost of having up to two partial weeks, one at either end, is often desired in accounting etc. and for that the YEAR() / WEEK() approach is better.

More Related Contents:

  1. MySQL Query GROUP BY day / month / year
  2. Group mysql query by 15 min intervals
  3. Using LIMIT within GROUP BY to get N results per group?
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. Error in MySQL when setting default value for DATE or DATETIME
  7. Retrieving the last record in each group – MySQL
  8. MySQL – Selecting a Column not in Group By
  9. Mysql query to dynamically convert rows to columns
  10. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  11. Timestamp with a millisecond precision: How to save them in MySQL
  12. Select the 3 most recent records where the values of one column are distinct
  13. Is there ANY_VALUE capability for mysql 5.6?
  14. How to convert number of week into date?
  15. Set NOW() as Default Value for datetime datatype?
  16. In SQL, how to select the top 2 rows for each group
  17. How to subtract hours from a datetime in MySQL?
  18. SQL Query to get column values that correspond with MAX value of another column?
  19. Returning the ‘last’ row of each ‘group by’ in MySQL
  20. MySql difference between two timestamps in days?
  21. MySQL: How do I join same table multiple times?
  22. How to get current date & time in MySQL?
  23. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  24. mysql select sum group by date
  25. how to cast the hexadecimal to varchar(datetime)?
  26. MySQL Group By Hours
  27. MySQL cumulative sum grouped by date
  28. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  29. group by range in mysql
  30. SQL query with avg and group by

Leave a Comment