How to Group by Month in MySQL
In this tutorial, we will learn how to group values by month in a MySQL database.
Businesses and organizations must find user or customer data based on their buying or using trends in months. A particular business might infer insightful data if it realizes the optimal months to boost the business, and MySQL assists us with this task.
MySQL provides us with the date_format()
function, containing two main values. First is the column name under consideration, and second is the time period for grouping.
Once our function is set up, we can group different time periods using the GROUP BY
clause in MySQL. The syntax of this operation is as follows.
select date_format(date_column, '%M'),sum(any_other_column)
from name_of_the_table
group by date_format(date_column, '%M');
This syntax assumes that we wish to group values of any_other_column
by month. Therefore, it provides the total of a particular column for each month in our table.
Let us see this method in action.
But before we begin, let us create a dummy dataset by creating a table, student_semester_date
with a few rows.
-- create the table student_semester_date
CREATE TABLE student_semester_date(
stu_id int,
stu_date date,
stu_marks int
);
Then let us insert a few rows in this table using the query below.
-- insert rows in the table student_semester_date
insert into student_semester_date(stu_id,stu_date,stu_marks)
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);
The above two queries create a table with rows with students’ first and last names.
SELECT * FROM student_semester_date;
Output:
stu_id stu_date stu_marks
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
Let us try to group the marks of different students based on the months from the stu_date
column. It would essentially entail counting the total marks for each month in our student_semester_date
table.
Group by Month in MySQL
As we have seen the syntax above, we can operate grouping marks by month in our table student_semester_date
with the help of the following query.
select date_format(stu_date, '%M') as Month,sum(stu_marks) as total_marks
from student_semester_date
group by date_format(stu_date, '%M');
The aforementioned code returns the total marks for each month in the student_semester_date
table.
It would mean that for November, we would have 400 as there are two entries in our table for November with marks 250 and 150 (250 + 150 = 400). The output of the aforementioned query is as follows.
Month total_marks
October 250
November 400
December 600
Month
and total_marks
in the aforementioned code for better readability with the AS
keyword in MySQL.Thus, with the help of the date format()
function and group by
statement, we can efficiently group values by month in a particular table in MySQL.