Sometimes you may need to aggregate data by month in MySQL. In this article, we will look at different ways to group by month in MySQL.
How to Group By Month in MySQL
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 an example to convert a date string into month.
mysql> SELECT DATE_FORMAT("2020-06-15", "%M");
+---------------------------------+
| DATE_FORMAT("2020-06-15", "%M") |
+---------------------------------+
| June |
+---------------------------------+
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,'2020-10-10',100),
(3,'2020-11-05',250),
(4,'2020-11-15',150),
(5,'2020-12-01',350),
(6,'2020-12-21',250);
mysql> select * from sales;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2020-10-01 | 150 |
| 2 | 2020-10-10 | 100 |
| 3 | 2020-11-05 | 250 |
| 4 | 2020-11-15 | 150 |
| 5 | 2020-12-01 | 350 |
| 6 | 2020-12-21 | 250 |
+------+------------+--------+
Also Read : How to Remove NOT NULL Constraint in MySQL
Group By Month Name
Here is the SQL query to group by month name.
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.
Also read : How to Remove Default Value of Column in MySQL
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 | 250 |
| November 2020 | 400 |
| December 2020 | 600 |
+----------------------------------+-------------+
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 |
+-------------------------------+-------------+
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
