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 in getting the summarized data from the raw resultset. It returns the reduced data based on certain column(s) or 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.

Some of the terms used in the above syntax are given below:

• 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): 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.
• criteriacolumn1 , criteriacolumn2,…,criteriacolumnj: These are the columns that will be considered as the criteria to create the groups in the MySQL query. 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. 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 

• The GROUP BY clause in MySQL is evaluated after the FROM, WHERE and SELECT clauses.
• Also the evaluation of HAVING, ORDER BY and LIMIT clauses are performed after the evaluation of the GROUP BY clause.

The following diagram illustrates the sequence of evaluation.

Examples of MySQL GROUP BY

Given below are the examples mentioned:

Let us create one table names developers using the following create 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 developers table using insert statement.

Code:

INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`) VALUES('2','1','Heena','Developer','Angular',10000);
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 developers table using the below SELECT query.

Code:

SELECT * FROM `developers`;

Output:

Example #1

Retrieving DISTINCT results using simple GROUP BY Clause.

Now, let us group the developers table based on the position column that will give us all the assigned positions list. For this, we will have to use the GROUP BY statement in the following way in SELECT query.

Code:

SELECT position FROM developers GROUP BY position;

Output:

This gives the list of all the distinct positions in the developers 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. For this to find the total salary, we will have to use the aggregate function SUM on column salary. Our SELECT query will look somewhat like 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.

Now, the next task is to find out the number of persons assigned to each technology. To find out the number of members, we will use aggregate function and as person count of each technology is to be found, we will group by based on 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 total salary of that team should exceed 40000 limit. 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 GROUP BY clause to aggregate results and get the summarized resultset from the original resultset. It can be used with aggregate functions and HAVING clause. Alias can also be used in GROUP BY clause’s criteria in MySQL.

Recommended Articles

This is a guide to MySQL GROUP BY. Here we discuss the evaluation of MySQL GROUP BY with query examples respectively. You may also have a look at the following articles to learn more –

1. MySQL Administration
2. IF Statement in MySQL
3. Unique Key in MySQL
4. MySQL Subquery