MySQL GROUP BY month

Introduction to MySQL GROUP BY month

MySQL GROUP BY is a MySQL clause for grouping a set of table rows into subgroups on the basis of values of expressions or columns. Thus, the GROUP BY clause reducing the number of table rows in the output set, the clause returns single row for every group specified in the query. The GROUP BY clause is a non compulsory clause of the SELECT statement in MySQL which is used together in the query. In additional to this, the GROUP BY CLAUSE can be defined with the application of certain functions like aggregate functions or date functions. With this implementation, we can group the table rows using month or year by applying DATE_FORMAT() in MySQL adding to the GROUP BY clause to display the result set grouping according to month part of DATE function.

Syntax:

Syntax structure for using the GROUP BY Month clause in MySQL:

SELECT DATE_FORMAT(ColumnName, ‘%m-%Y’) FROM TableName
GROUP BY MONTH (ColumnName) , YEAR (ColumnName) DESC;

For displaying the result set with GROUP BY Month clause we will use this syntax which is described as follows:

• The MySQL operator DATE_FORMAT() is used here so that it will be easy enough to group the timestamp, date, or datetime table column implementing any format needed by your result to show in the query section.
• TableName denotes the particular database table involved in the GROUP BY Month clause.
• ColumnName defines the table column which you want to group using the month or year formats. The column data type should have either, DATETIME, TIMESTAMP or DATE so that while using the GROUP BY Month clause the month part can be properly extracted based on the column from the table data in database.
• We can even sort the result set or date column values either using ASC or DESC options.

How GROUP BY month works in MySQL?

• In MySQL, the SELECT statement is often applied to fetch all the records that match each other from the database tables on the basis of several clauses which are used in the query statements.
• It helps to provide summary of type of data that we want to retrieve from the tables based on any table field or column values or it may be any aggregate functions used like AVG, SUM, MIN, MAX, etc. in MySQL. Others functions like DATE function with their respective formats are also used.
• The GROUP BY clause along with SELECT query is used to display the result rows in grouping order with the help of column and data type in MySQL. Generally, the GROUP BY clause should appear after the two clauses, FROM and WHERE in MySQL query to specify the grouping column with function.
• Normally, the MONTH() function is defined to identity every month and aggregate functions helps to show the calculative values of sum, average, minimum, maximum and so on.
• Thus, we will use GROUP BY Month to fetch the table records in the output set by grouping the column with month part of the Date function provided in the DATE_FORMAT() structure.

Examples of MySQL GROUP BY month

Given below are the examples mentioned :

Example #1

Simple Example using GROUP BY Month with DATE_FORMAT().

To recognize the above concept of GROUP BY Month clause, let us built up a table to show the results of rows from the table that is grouped by month part of the DATE_FORMAT().

Suppose, we have the following query code to create a table.

Code:

CREATE TABLE DemoGroupMonth(Group_ID INT PRIMARY KEY, Due_Date DATE);

Again, we will enter the sample records in the table by using the following statement.

Code:

INSERT INTO DemoGroupMonth VALUES(1,NOW());
INSERT INTO DemoGroupMonth VALUES(2,DATE_ADD(NOW(), INTERVAL 3 year));
INSERT INTO DemoGroupMonth VALUES(3,DATE_ADD(NOW(), INTERVAL -3 year));
INSERT INTO DemoGroupMonth VALUES(4,DATE_ADD(NOW(), INTERVAL 2 year));
INSERT INTO DemoGroupMonth VALUES(5,DATE_ADD(NOW(), INTERVAL -2 year));
INSERT INTO DemoGroupMonth VALUES(6,DATE_ADD(NOW(), INTERVAL 1 year));

Now, we will view all the records from this table DemoGroupMonth by applying the SELECT statement as follows.

Code:

SELECT * FROM DemoGroupMonth;

Output:

After this, we will code for retrieving the result set with GROUP BY Month.

Code:

SELECT DATE_FORMAT(Due_Date , '%m') AS Month_Group FROM DemoGroupMonth GROUP BY MONTH(Due_Date) DESC;

The succeeding output is produced which displays month grouped by implementing GROUP BY.

Output:

As you can see the DATE column values are displayed in the result set by grouping with month and in descending order.

Suppose, we want to count the number of records of the table to be determined or calculated using a specific period of time such as a Day, Month or Year parts of DATE function where the column will be having DATETIME, DATE or TIMESTAMP field data type, then the simple query will be as follows.

Code:

SELECT COUNT(Group_ID) FROM demogroupmonth WHERE YEAR(Due_Date)='2023' GROUP BY MONTH(Due_Date);

Output:

For this we have again inserted some rows in the table having year equal to 2023 to make the result properly runs as.

Code:

select * from demogroupmonth;

Output:

Example #2

Example using GROUP BY Month with same date.

If we want group table rows which are added on the identical date or day, then we will query the count of such grouped rows as follows with GROUP BY Month.

Code:

Select COUNT(*), DATE_FORMAT(Due_Date,'%m') as Month_Created_At FROM demogroupmonth GROUP BY Month_Created_At;

Output:

Example #3

Example using GROUP BY Month for Employee month wise data.

Assume that we have a table in our database as Employee which includes fields such as Person_ID, Employee_Name, Salary, JoinDate.

Code:

select * from employee

Output:

Here the JoinDate column of the table contains DATE data type values to perform the grouping of rows by month.

Code:

SELECT Employee_Name, Salary, DATE_FORMAT(JoinDate , '%m') AS Emp_Month_Group FROM employee GROUP BY MONTH(JoinDate) ASC;

Output:

Like this we can query any employee data to find the records month wise from the Employees table in the database implementing the MySQL GROUP BY month clause.

Conclusion

The MySQL GROUP BY month clause is responsible to group to provide a set of rows grouped by the EXTRACT function that permits to abstract parts of a date value like month, year, day or time. The MySQL GROUP BY month clause is useful to find out the monthly report in sales database tables to produce the systematic data of the employees.

This is a guide to MySQL GROUP BY month. Here we discuss the introduction to MySQL GROUP BY month, how does it works along with query examples. You may also have a look at the following articles to learn more –

1. MySQL AUTO_INCREMENT
2. MySQL REINDEX
3. MySQL Admin Tool
4. MySQL CTE