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.