EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL GROUP BY

MySQL GROUP BY

By Payal UdhaniPayal Udhani

MySQL GROUP BY

Introduction to MySQL GROUP BY

The following article provides an outline for MySQL GROUP BY. In MySQL, various clauses can be used with the SELECT clause to achieve some specific functionality or make the resultset to be retrieved in a particular format. GROUP BY clause is one such clause that helps get the summarized data from the raw resultset. It returns the reduced data based on specific column(s), expression (s), or a combination of both as the criteria to group the data. Only one row is returned corresponding to each group defined by the grouping criteria of columns and expressions in the SELECT query. Most of the time, the GROUP BY function is used when using the aggregate functions of MySQL such as MAX, MIN, AVG, SUM, and COUNT so that the final result will contain the aggregated value of each group specified by criteria in GROUP BY clause.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

SELECT
column1, column2,..., columnm, aggregate_function(columni)
FROM
target_table
WHERE
conditions_or_constraints
GROUP BY criteriacolumn1 , criteriacolumn2,...,criteriacolumnj;

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.

The above syntax uses the following terms:

  • column1, column2,…, columnm: These are the names of the columns of the target_table table that need to be retrieved and fetched in the resultset.
  • aggregate_function(columni): We need to retrieve these aggregate functions defined on the columns of the target_table from the SELECT query.
  • target_table: Name of the table from where the result will be fetched.
  • conditions_or_constraints: If you want to apply certain conditions on specific columns, they can be mentioned in the optional WHERE clause.
  • criteriacolumn1, criteriacolumn2,…,criteriacolumnj: We will consider these columns as the criteria for creating the groups in the MySQL query. There can be single or multiple-column names to which the criteria must be applied. We can even mention expressions as the grouping criteria. Unlike the standard SQL, MySQL allows using the alias as the grouping criteria in the GROUP BY clause. Note that multiple criteria of grouping should be mentioned in a comma-separated format.

Evaluation of MySQL GROUP BY 

  • MySQL evaluates the GROUP BY clause after the FROM, WHERE, and SELECT clauses.
  • MySQL evaluates the HAVING, ORDER BY, and LIMIT clauses after evaluating the GROUP BY clause.

The following diagram illustrates the sequence of evaluation.

Evaluation

Examples of MySQL GROUP BY

The examples mentioned are as follows:

Let us create one table names developers using the following a query.

Code:

CREATE TABLE 'developers' (
'developer_id' int(11) NOT NULL,
'team_id' int(11) NOT NULL,
'name' varchar(100) DEFAULT NULL,
'position' varchar(100) DEFAULT NULL,
'technology' varchar(100) DEFAULT NULL,
'salary' int(11) DEFAULT NULL,
PRIMARY KEY ('developer_id','team_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Let us insert some records in the developer’s table using the insert statement.

Code:

INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology' 'salary') VALUES('1','4','Saraswati','Team Leaded','Java',15000);
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('2','1','Heena','Developer','Angular','10000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('3','3','Vishnu','Manager','Maven','25000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('4','3','Rahul','Support','Digital Marketing','15000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('5','3','Siddhesh','Tester','Maven','20000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('6','7','Siddharth','Manager','Java','25000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('7','4','Brahma','Developer','Digital Marketing','30000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('8','1','Arjun','Tester','Angular','19000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('9','2','Nitin','Developer','MySQL','20000');
INSERT INTO 'developers' ('developer_id', 'team_id', 'name', 'position', 'technology', 'salary') VALUES('10','2','Ramesh','Administrator','MySQL','30000');

We have inserted multiple values in my table using the same insert format.

Now, after retrieving the records of the developer’s table using the below SELECT query.

Code:

SELECT * FROM 'developers';

Output:

MySQL GROUP BY 1

Example #1 – Using GROUP BY Clause

Let us group the developer’s table based on the position column that will give us the list of assigned positions. For this, we will have to use the GROUP BY statement in the following way in the SELECT query.

Code:

SELECT position FROM developers GROUP BY position;

Output:

MySQL GROUP BY 2

This lists all the distinct positions in the developer’s table.

Example #2 – Using SUM Aggregate Function

Now, let us find out the result such that the team ids and the total salary that needs to be given to each team need to find out. To find the total salary, we must use the aggregate function SUM on the column salary. Our SELECT query will look somewhat like the following.

Code:

SELECT team_id,SUM(salary) FROM developers GROUP BY team_id;

Output:

Using SUM Aggregate Function

That displays the team_id and the total salary corresponding to that team that needs to be given.

Example #3 – Using COUNT Aggregate Function

The next task is determining the number of persons assigned to each technology. We will use the aggregate function to find out the number of members, and we will group based on technology to find the person count of each technology. Our SELECT statement will be as follows.

Code:

SELECT COUNT(*),technology FROM developers GROUP BY technology;

Output:

Using COUNT Aggregate Function

The above result shows that there are 2 members for every technology.

Example #4 – Using HAVING Clause

Let us now have the team ids for which we have to pay more than 40000 as the salary. For this, we will have to group the resultset based on team_id, and then by using the having clause, we will mention that the team’s total salary should exceed the 40000 limits. The SELECT query for getting the expected resultset will be as follows.

Code:

SELECT team_id,SUM(salary) FROM developers GROUP BY team_id HAVING SUM(salary)>40000;

Output:

Using HAVING Clause

Conclusion

We can use the GROUP BY clause to aggregate results and get the summarized resultset from the original resultset. MySQL users commonly utilize the GROUP BY function in conjunction with aggregate functions and the HAVING clause. Additionally, it is possible to employ an alias in the criteria of the GROUP BY clause.

Recommended Articles

We hope that this EDUCBA information on “MySQL GROUP BY” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Administration
  2. IF Statement in MySQL
  3. Unique Key in MySQL
  4. MySQL Subquery
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
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

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

EDUCBA

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

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
EDUCBA

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

Forgot Password?

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