Back to cookbooks list Articles Cookbook

How to Group by Month in MySQL


You'd like to group records by month in a MySQL database.


Our database has a table named hoodie with data in the columns id, color, and production_timestamp.

1Celestial Blue2022-02-01 11:45:23
2Pearled Ivory2022-02-01 11:46:13
3Blush2022-01-22 17:22:05


You can use the MONTH() and YEAR() functions to group records in a table by month. Use the DATE_FORMAT() function to display a label for the month.

Here's the query you would write:

  DATE_FORMAT(production_timestamp, ‘%m-%Y’) AS production_month,
  COUNT(id) AS count
FROM hoodie

Here’s the result of the query:



Discover the best interactive MySQL courses

Grouping records by month is a very common operation in MySQL. In our example, the number of products is totaled for each month. You usually want to take into account both the year and the month, i.e. you want to group events in January 2022 separately from events in January 2021.

To group data by month in MySQL, use the MONTH() and YEAR() functions. The MONTH() function extracts the number of the month (1 for January, 2 for February, etc.) from the date. The YEAR() function extracts the year from the date. When you combine the functions in the GROUP BY clause, you group the hoodies by the month and year they were produced.

We use DATE_FORMAT() to display a readable label for the month. It retrieves a date or time with a specific precision from a MySQL database. Here, we used month and month-year precisions.

DATE_FORMAT() takes two arguments: the timestamp value (in our example, the column production_timestamp) followed by the date part specifier (here, '%m-%Y'). The specifier is a string and must be enclosed in quotes. The %m is a placeholder for the month as a numeric value (00 to 12) and the %Y stands for the year as a 4-digit numeric value. In our database, the Blush hoodie has the production date 2022-01-22 17:22:05; after it's been truncated to month precision, it becomes 01-2022.

Recommended courses:

Recommended articles:

See also: