EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL GROUP BY month
Secondary Sidebar
MySQL Tutorial
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL GROUP BY month

By Aanchal SharmaAanchal Sharma

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)
  • 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:

with DATE_FORMAT()

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:

for retrieving the result set

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:

MySQL GROUP BY month 3

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:

MySQL GROUP BY month 4

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:

with same date

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:

MySQL GROUP BY month 6

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:

MySQL GROUP BY month 7

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.

Recommended Articles

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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more