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:

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.

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:

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:

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:

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:

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:

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