Last updated on July 16th, 2024 at 05:44 am
MySQL database is commonly used to store date time information such as sign ups, sales, payments, and other transactional data. It offers tons of functions and features to aggregate data by different time intervals such as years, months, weeks, days, etc. Often database programmers and data analysts need to aggregate data by month in MySQL. This is commonly required for online stores, apps, ecommerce businesses. It is also required for MIS reporting teams. In this article, we will look at different ways to group by month in MySQL.
How to Group By Month in MySQL
There are several simple ways to aggregate MySQL data by month. MySQL provides built-in functions such as year(), month() and day() to group data by year, month and day respectively. It also provides a powerful function date_format() that allows you to group data any way you want by simply changing the format string of its output. We will look at how to group by month in MySQL using each of these methods.
Let us say you have a table sales(id, order_date, amount)
mysql> create table sales(id int, order_date date, amount int);
mysql> insert into sales(id,order_date,amount)
values(1,'2020-10-01',150),
(2,'2021-10-10',100),
(3,'2020-11-05',250),
(4,'2021-11-15',150),
(5,'2020-12-01',350),
(6,'2021-12-21',250);
mysql> select * from sales;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2020-10-01 | 150 |
| 2 | 2021-10-10 | 100 |
| 3 | 2020-11-05 | 250 |
| 4 | 2021-11-15 | 150 |
| 5 | 2020-12-01 | 350 |
| 6 | 2021-12-21 | 250 |
+------+------------+--------+
1. Using month, monthname and year
MySQL provides many built in functions such as month(), monthname() and year() to aggregate data at different levels of granularity. We will use them to obtain monthly data.
Using Month()
Month() function returns the month number of a date string. You can pass this string as a literal value of column name. Here is a SQL query to extract month number from order_date column and aggregates the value of amount column to obtain monthly sales numbers.
mysql> SELECT MONTH(order_date) AS month, sum(amount) AS total_sales
FROM sales
GROUP BY MONTH(order_date);
+-------+-------------+
| month | total_sales |
+-------+-------------+
| 10 | 250 |
| 11 | 400 |
| 12 | 600 |
+-------+-------------+
Please note, you need to mention the month() function in select clause as well as group by clause. If you do not mention it in group by clause, MySQL will not do the aggregation. Here is an example where we have not mentioned month() function in group by.
mysql> SELECT MONTH(order_date) AS month, sum(amount) AS total_sales
FROM sales
GROUP BY order_date;
+-------+-------------+
| month | total_sales |
+-------+-------------+
| 10 | 150 |
| 10 | 100 |
| 11 | 250 |
| 11 | 150 |
| 12 | 350 |
| 12 | 250 |
+-------+-------------+
Using MonthName()
monthname() function returns the name of the month, instead of month number as in case of month() function. It’s syntax is similar to that of month() function. Here is the SQL query to aggregate data by month name.
mysql> SELECT MONTHNAME(order_date) AS month, sum(amount) AS total_sales
FROM sales
GROUP BY MONTHNAME(order_date);
+----------+-------------+
| month | total_sales |
+----------+-------------+
| December | 600 |
| November | 400 |
| October | 250 |
+----------+-------------+
Please note, here also you need to mention monthname() function in both select as well as group by clause for proper aggregation. Secondly, the result of monthname() function is sorted alphabetically whereas the result of month() function is sorted numerically or chronologically.
If you want to chronologically sort output of monthname() function, then use monthname() only in select clause but use month() function in group by as shown below.
mysql> SELECT MONTHNAME(order_date) AS month, sum(amount) AS total_sales
FROM sales
GROUP BY MONTH(order_date);
+----------+-------------+
| month | total_sales |
+----------+-------------+
| October | 250 |
| November | 400 |
| December | 600 |
+----------+-------------+
The above output is not sorted alphabetically but chronologically.
Using Year()
MySQL also provides year() function that extracts year number from a date. If your data runs into multiple years, as in our case, then grouping data based on month alone will end up aggregating data of different years into a single month. In such cases, you will need to group by month as well as year. Here is an SQL query to group data by year and month.
mysql> SELECT YEAR(order_date),MONTH(order_date) AS month,
sum(amount) AS total_sales
FROM sales
GROUP BY year(order_date), month(order_date);
+------------------+-------+-------------+
| YEAR(order_date) | month | total_sales |
+------------------+-------+-------------+
| 2020 | 10 | 150 |
| 2020 | 11 | 250 |
| 2020 | 12 | 350 |
| 2021 | 10 | 100 |
| 2021 | 11 | 150 |
| 2021 | 12 | 250 |
+------------------+-------+-------------+
Please note, you will need to mention both year() and month() functions in SELECT as well as GROUP BY clauses of your SQL queries. Otherwise, the aggregation will not work properly.
In above case, the groups are sorted by year first and within each year, they are sorted by month. You can change the order of your output using ORDER BY clause as shown below. Here we have sorted the year in descending order, using desc keyword and within each year, we have sorted the month in ascending order.
mysql> SELECT YEAR(order_date),MONTH(order_date) AS month,
sum(amount) AS total_sales
FROM sales
GROUP BY year(order_date), month(order_date)
ORDER BY year(order_date) desc, month(order_date);
+------------------+-------+-------------+
| YEAR(order_date) | month | total_sales |
+------------------+-------+-------------+
| 2021 | 10 | 100 |
| 2021 | 11 | 150 |
| 2021 | 12 | 250 |
| 2020 | 10 | 150 |
| 2020 | 11 | 250 |
| 2020 | 12 | 350 |
+------------------+-------+-------------+
2. Using date_format
Date_format() is a powerful function that allows you to format your date into any kind of string. We will use date_format function to format date into month values and group by month.
Here is the syntax of date_format function.
date_format(value, format string)
In the above function, you need to specify value as a literal, another function or column name, and a format string that specifies the format to which this value needs to be converted to. Here is a list of format strings supported by date_format.
Here is an example to convert a date string into month.
mysql> SELECT DATE_FORMAT("2020-06-15", "%M");
+---------------------------------+
| DATE_FORMAT("2020-06-15", "%M") |
+---------------------------------+
| June |
+---------------------------------+
Group By Month Name
Here is the SQL query to group by month name using %M format specifier.
mysql> select date_format(order_date, '%M'),sum(amount)
from sales
group by date_format(order_date, '%M');
+-------------------------------+-------------+
| date_format(order_date, '%M') | sum(amount) |
+-------------------------------+-------------+
| December | 600 |
| November | 400 |
| October | 250 |
+-------------------------------+-------------+
In the above SQL query we use date_format(order_date, “%M”) to convert a date column into month name, and use sum column to add up the sales amounts.
Group By Month and Year
Here is the SQL query to group by month and year.
mysql> select date_format(order_date, '%M %Y'),sum(amount)
from sales
group by year(order_date),month(order_date);
+----------------------------------+-------------+
| date_format(order_date, '%M %Y') | sum(amount) |
+----------------------------------+-------------+
| October 2020 | 150 |
| November 2020 | 250 |
| December 2020 | 350 |
| October 2021 | 100 |
| November 2021 | 150 |
| December 2021 | 250 |
+----------------------------------+-------------+
In the above query, we use date_format to convert date column’s values into month and year names. We also use YEAR() and MONTH() functions to ensure that data is grouped and ordered by month and year numbers.
In the above query, if we use date_format function in group by clause, then MySQL will sort the groups alphabetically, instead of chronologically. See below.
mysql> select date_format(order_date, '%M'),sum(amount)
from sales
group by date_format(order_date, '%M');
+-------------------------------+-------------+
| date_format(order_date, '%M') | sum(amount) |
+-------------------------------+-------------+
| December | 600 |
| November | 400 |
| October | 250 |
+-------------------------------+-------------+
Conclusion
In this article, we have learnt several different ways to group data by month in MySQL. You can use any of them as per your requirement. But there are some important things to keep in mind. First of all, you need to mention the aggregation function month(), year(), monthname() or date_format() in both SELECT as well as GROUP BY parts of your SQL query. If you do not mention them in GROUP BY clause, then MySQL will not do the aggregation properly.
Second, remember that month() and year() functions will sort the output chronologically while month_name() and date_format() will sort the output alphabetically. You can always change the sort order by adding ORDER BY clause as we have shown above.
We have covered many subtle nuances about this problem in this article. Please go through them carefully and use the appropriate solution to avoid any pitfalls.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also Read : How to Remove NOT NULL Constraint in MySQL
Also read : How to Remove Default Value of Column in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.