Updated March 10, 2023
Introduction to SQL GROUP BY Month
SQL GROUP BY month is used to get the grouped data in a summarized way based on each of the months in SQL tables. In scenarios, where we require monthly data of the organization, sales, submissions, tests, etc we can make the use of group by clause and define the grouping criteria as the month record value present in the table records or simply retrieve the month value using functions in SQL from the date and time data typed column values to group the result based on the month. In this article, we will study the syntax of grouping the result set of the query statement using SQL group by month, it’s working, and multiple examples to retrieve the column value from the resultset to apply it as grouping criteria.
Syntax:
The syntax of the group by month clause is as follows –
SELECT
column1, column2,..., columnm, aggregate_function(columni)
FROM
target_table
WHERE
conditions_or_constraints
GROUP BY expressionDerivingMonthOfColumn ;The syntax of the GROUP BY clause is as shown above. It is the optional clause used in the select clause whenever we need to summarize and reduce the resultset. It should always be placed after the FROM and WHERE clause in the SELECT clause. Some of the terms used in the above syntax are explained below –
- column1, column2,…, columnm – These are the names of the columns of the target_table table that need to retrieved and fetched in the resultset.
- aggregate_function(columni) – These are the aggregate functions defined on the columns of target_table that needs to be retrieved from the SELECT query.
- target_table – Name of the table from where the result is to be fetched.
- conditions_or_constraints – If you want to apply certain conditions on certain columns they can be mentioned in the optional WHERE clause.
- expressionDerivingMonthOfColumn – This is the column that will be considered as the criteria to create the groups in the MYSQL query based on month value. There can be single or multiple column names on which the criteria need to be applied. We can even mention expressions as the grouping criteria that can include functions available in SQL to retrieve month from the date and time-related data types. It can also be a single column name that stores month value for that records.
Examples of SQL GROUP BY Month
Consider a table named educba_ articles having the contents and structure as shown in the output of the following select query statement.
SELECT * FROM educba_articles;The output of the execution of the above query statement is as follows showing the structure and contents of educba_ articles table-
Now, we will group the resultset of the educba_ articles table contents based on a column named month so that the retrieved records will only a single record for the rows having the same values for month and data will be seen in a monthly format. Also, we will retrieve the average rate per month and the name of the month from the query statement.
Our query statement will be as follows –
SELECT
AVG(a.` rate`),a.` month`
FROM
educba_articles a
GROUP BY a.` month`;The output of the execution of the above query statement is as follows –
We can observe that the output contains all three months and the result set has been grouped monthly and the rate value that is retrieved is the average rate for each of the months.
Now, we will retrieve the records of the educba_articles table who have status as submitted and the retrieved records should contain the sum of the rate column for each of the month and the name of the month. For this, we will first need to restrict the where clause statement by applying the condition to retrieve only submitted articles and further group the records based on month column and apply SUM() function on rate column to retrieve the total rate for each of the month for submitted articles.
Our query statement will be as shown below –
SELECT
SUM(a.` rate`),a.` month`
FROM
educba_articles a
WHERE ` status` = "Submitted"
GROUP BY a.` month`;The output of the execution of the above query statement is as follows –
Now, consider a table named educba_ writers having the contents and structure as shown in the output of the following select query statement –
SELECT * FROM educba_writers;The output of the execution of the above query statement is as follows showing the structure and contents of educba_ writers table –
Now, we want to group the rate column based on the month of joining each of the writers. To get the monthly data we will first need to retrieve the month from the joining date and time column and apply that value as the grouping criteria. Further, we want to retrieve the sum of the rate in a monthly format. Hence, we will use the aggregate function named SUM() to get the total value of the rate column.
Our query statement will be as shown below –
SELECT
SUM(rate),MONTH(joining_date_time)
FROM
educba_writers
GROUP BY MONTH(joining_date_time );The output of the execution of the above query statement is as follows showing the months total rate in educba_writers table –
From the above output, we can observe that the total rate of each month is retrieved. You can cross-check by going the total rate column for each month. For example, the writers named vyankatesh and samiksha joined in the sixth month of 2020 and hence while grouping their rates will lead to a total of 700.00 and 980.23 that is equivalent to 1680.23 value. Similarly, we can confirm for other months.
Conclusion
We can retrieve the data in a month’s format by grouping the resultset based on the month. For this, some of the columns of the table from which the data is being required must contain the month value or date value from where the month can be retrieved. Month value can be retrieved from the date-time data typed column using the SQL function MONTH(). This is done mostly for building queries for reports.
Recommended Articles
We hope that this EDUCBA information on “SQL GROUP BY Month” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
