MySQL GROUP BY WEEK tutorial


The MySQL WEEK() function is commonly used to get the week number representing a timestamp record in your MySQL table.

This function can also be used to group the result set of your SELECT query to produce a report from your table data.

For example, suppose you have a table named Invoices that stores all invoices created for your business:

+----+---------------------+--------+
| id | invoice_date        | amount |
+----+---------------------+--------+
|  1 | 2021-11-01 14:51:03 |    200 |
|  2 | 2021-11-03 14:51:03 |    150 |
|  3 | 2021-11-10 14:51:32 |    175 |
|  4 | 2021-11-18 14:51:45 |    225 |
|  5 | 2021-12-03 14:52:51 |    210 |
+----+---------------------+--------+

Now you want to group these invoice records by the week of the invoice_date column and count how many invoices were created for that week.

Here’s the SELECT statement to do so:

SELECT WEEK(invoice_date) AS Week, COUNT(*) AS Total
FROM Invoices
GROUP BY Week;

The result will be as follows:

+------+-------+
| Week | Total |
+------+-------+
|   44 |     2 |
|   45 |     1 |
|   46 |     1 |
|   48 |     1 |
+------+-------+

But now the result set isn’t really clear, so you may want to add the YEAR of the invoices as follows:

SELECT YEAR(invoice_date) AS Year, 
  WEEK(invoice_date) AS Week, 
  COUNT(*) AS Total
FROM Invoices
GROUP BY Year, Week

The returned response will be as shown below:

+------+------+-------+
| Year | Week | Total |
+------+------+-------+
| 2021 |   44 |     2 |
| 2021 |   45 |     1 |
| 2021 |   46 |     1 |
| 2021 |   48 |     1 |
+------+------+-------+

If you want the Week column to be more readable, you can specify the DATE_FORMAT() function instead of the WEEK() function.

Consider the following example:

SELECT YEAR(invoice_date) AS Year, 
  DATE_FORMAT(invoice_date, '%b %u') AS Week, 
  COUNT(*) AS total
FROM Invoices
GROUP BY Year, Week;

The output will be as follows:

+------+--------+-------+
| Year | Week   | total |
+------+--------+-------+
| 2021 | Nov 44 |     2 |
| 2021 | Nov 45 |     1 |
| 2021 | Nov 46 |     1 |
| 2021 | Dec 48 |     1 |
+------+--------+-------+

Unfortunately, MySQL doesn’t have a built-in function to count the week of the month to produce the following result:

+------+--------+-------+
| Year | Week   | total |
+------+--------+-------+
| 2021 | Nov 01 |     2 |
| 2021 | Nov 02 |     1 |
| 2021 | Nov 03 |     1 |
| 2021 | Dec 01 |     1 |
+------+--------+-------+

You can only return the week of the year when using the DATE_FORMAT() function.

And that’s how you group the result set by week and year values in MySQL.

Get 50 MySQL Snippets Book for FREE 🔥

50 MySQL snippets that you can use in various scenarios

Save 1000+ hours of research and 10x your productivity