How to Group by Month in MySQL

Preet Sanghavi Feb 02, 2024
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
Note
We use the alias 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.

Preet Sanghavi avatar Preet Sanghavi avatar

Preet writes his thoughts about programming in a simplified manner to help others learn better. With thorough research, his articles offer descriptive and easy to understand solutions.

LinkedIn GitHub

Related Article - MySQL Query