JIYIK CN >

In this article, we will learn how to group values ​​by month in MySQL database.

Businesses and organizations have to find user or customer data based on purchase or usage trends over a few months. If a particular business achieves its best month for boosting business, it may be able to infer insightful data, and MySQL can help us accomplish this task.

MySQL provides us with date_format()the function which takes two main values. First is the column name to be considered and second is the time period to group by.

After setting the function, we can use the clause in MySQL GROUP BYto group different time periods. The syntax for 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 want any_other_columnto group the values ​​of by month. Hence, it provides the total for a particular column for each month in our table.

Let’s see this approach in action.

But before we begin, let's student_semester_datecreate a dummy dataset by creating a table 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 into this table using the following query.

-- 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 containing the first and last names of students.

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's try stu_dateto group the scores of different students based on the month in the column. It basically requires calculating the total score for each month in our student_semester_datetable.

Grouping by month in MySQL

student_semester_dateAs we have seen in the above syntax, we can operate grouping token by month in our table with the help of 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 above code student_semester_datereturns the total score for each month in the table.

This means that for November, we will have 400 since we have two entries in our table for November labeled 250 and 150 (250 + 150 = 400). The output of the above query is given below.

Month       total_marks
October		250
November	400
December	600

Thus, with the help of date format()the function and group bythe statement, we can effectively group the values ​​by month in a particular table in MySQL.