MySQL GROUP BY WEEK tutorial

Posted on Dec 09, 2021

Learn how to group your MySQL result set by WEEK of the time record

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.

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.