How to group the result set returned by MySQL?

This can be accomplished by using the GROUP BY clause in the SELECT statement. We can specify columns as grouping criteria with the help of GROUP BY clause. Rows with the same value in a specific column are considered a single group due to the specified grouping criteria. In this way, the result set returned by the MySQL SELECT statement is grouped.

Example

Here is a good example to understand it-

We have a table named "employees" as follows-

mysql> Select * from employees;
+------+-------------+--------+------------+
| id   | designation | Salary | DoJ        |
+------+-------------+--------+------------+
| 100  | Asst.Prof   | 50000  | 2016-06-15 |
| 300  | Prof        | 85000  | 2010-05-18 |
| 250  | Asso.Prof   | 74000  | 2013-02-12 |
| 400  | Prof        | 90000  | 2009-05-19 |
| 200  | Asst.Prof   | 60000  | 2015-05-11 |
+------+-------------+--------+------------+
5 rows in set (0.00 sec)

Now with the help of the following script, we group the output;

mysql> select designation, count(*), AVG(salary) from employees group by designation;
+-------------+----------+-------------+
| designation | count(*) | AVG(salary) |
+-------------+----------+-------------+
| Asso.Prof   | 1        | 74000.0000  |
| Asst.Prof   | 2        | 55000.0000  |
| Prof        | 2        | 87500.0000  |
+-------------+----------+-------------+
3 rows in set (0.00 sec)

The result set returned by the above query is Asso.Prof, 1 in total, with an average salary of 74000, and Asst.Prof, 2 in total, with an average salary is 55,000, Prof, 2 in total, the average salary is 74,000. The average salary is 87,500.

The above is the detailed content of How to group the result set returned by MySQL?. For more information, please follow other related articles on the PHP Chinese website!