A brief discussion on group by in MySQL

1. Introduction
2. Prepare the user table

2.1 Group by rules
2.2 Use of group by
2.3 Use of having
2.4 order by and limit
2.5 with rollup

1. Introduction

MySQL 's group by is used to group queried data; in addition, MySQL provides having clause to filter the data within the group.

MySQL provides many select clause keywords.

Their order in the statement is as follows:

Clauses	effect	Is it necessary/when to use
select	Query the data or expression to be returned	yes
from	Specify the table to query	no
where	Specify row-level filtering	no
group by	Grouping	No/Use when grouping data
having	Packet filtering	No/Use to filter the grouped data
order by	Specify sorting rules when returning data	no
limit	Specify the number of rows to return	no

2. Prepare the user table

Prepare a user table, whose DDL and table data are as follows

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'User name',
  `nation` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Ethnicity',
  `age` int(11) NULL DEFAULT NULL COMMENT 'Age',
  `height` double NULL DEFAULT NULL COMMENT 'Height',
  `sex` smallint(6) NULL DEFAULT NULL COMMENT 'Gender',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '李子八', '汉族', 18, 180, 1);
INSERT INTO `user` VALUES (2, '张三', '慧族', 20, 175, 1);
INSERT INTO `user` VALUES (3, '李四', 'Uyghur', 45, 168, 0);
INSERT INTO `user` VALUES (4, '王五', '蒙古族', 18, 177, 1);
INSERT INTO `user` VALUES (5, '赵六', '汉族', 16, 184, 0);
INSERT INTO `user` VALUES (6, '田七', 'Uygur', 27, 192, 1);

The data in the user table is as follows:

mysql> select * from user;
+----+--------+----------+------+--------+------+
| id | name | nation | age | height | sex |
+----+--------+----------+------+--------+------+
| 1 | Li Ziba | Han nationality | 18 | 180 | 1 |
| 2 | Zhang San | Hui nationality | 20 | 175 | 1 |
| 3 | Li Si | Uyghur | 45 | 168 | 0 |
| 4 | Wang Wu | Mongolian | 18 | 177 | 1 |
| 5 | Zhao Liu | Han nationality | 16 | 184 | 0 |
| 6 | Tianqi | Uyghur | 27 | 192 | 1 |
+----+--------+----------+------+--------+------+
6 rows in set (0.00 sec)

2.1 Group by rules

Before using group by you need to understand the relevant rules of group by

group by clause is placed after where and before order by clause
having clause is placed after the group by clause and before the order by clause.
Each column in group by clause must be a select column or a valid expression. Aggregate functions cannot be used.
The expression used in select must appear in the group by clause and cannot use aliases
The data group by group contains null values, and the null values are grouped into one group.
group by clause can be nested, and the nested groups are summarized on the last group.

2.2 Use of group by

need:

Count the number of users of different nationalities

Statement:

mysql> select nation, count(*) from user group by nation;
+----------+----------+
| nation | count(*) |
+----------+----------+
| Han nationality| 2 |
| Hui Nationality| 1 |
| Uyghur | 2 |
| Mongolian| 1 |
+----------+----------+
4 rows in set (0.00 sec)

GROUP BY can be used in conjunction with where , but where cannot be used for filtering after group by . After using the where clause, the grouped data is the data set filtered by the WHERE clause.

mysql> select nation, count(*) as nation_num from user where sex = 0 group by nation;
+----------+------------+
| nation | nation_num |
+----------+------------+
| Uyghur | 1 |
| Han nationality| 1 |
+----------+------------+
2 rows in set (0.00 sec)

2.3 Use of having

If the data after grouping group by group by needs to be filtered again, having clause must be used. MySQL server throws an exception when using where clause after group by clause

mysql> select nation, count(*) as nation_num from user group by nation where nation = '汉族';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where nation = '汉族'' at line 1

At this point, you only need to replace the above where clause with the having clause. having clause supports all where operators. In layman's terms, the where clause can only be used after group by clause if it is replaced with having

vmysql> select nation, count(*) as nation_num from user group by nation having nation = '汉族';
+--------+------------+
| nation | nation_num |
+--------+------------+
| Han nationality| 2 |
+--------+------------+
1 row in set (0.00 sec)

2.4 order by and limit

If the grouped data needs to be sorted, you can use order by . order by clause needs to be after having clause.

mysql> select nation, count(*) as nation_num from user group by nation having nation != '汉族' order by nation_num desc;
+----------+------------+
| nation | nation_num |
+----------+------------+
| Uyghur | 2 |
| Hui Nationality| 1 |
| Mongolian| 1 |
+----------+------------+
3 rows in set (0.00 sec)

If you need to specify the number of rows to be returned for the output results, you can use limit . The limit clause is at the end of the entire statement.

mysql> select nation, count(*) as nation_num from user group by nation having nation != '汉族' order by nation_num desc limit 2;
+----------+------------+
| nation | nation_num |
+----------+------------+
| Uyghur | 2 |
| Hui Nationality| 1 |
+----------+------------+
2 rows in set (0.00 sec)

2.5 with rollup

In the group by clause, WITH ROLLUP can perform the same statistics (SUM, AVG, COUNT...) based on the grouped statistical data.

For example, max():

mysql> select nation, max(height) as nation_num from user group by nation with rollup;
+----------+------------+
| nation | nation_num |
+----------+------------+
| Hui Nationality| 175 |
| Han nationality| 184 |
| Uyghur | 192 |
| Mongolian | 177 |
| NULL | 192 |
+----------+------------+
5 rows in set (0.00 sec)

For example, avg():

mysql> select nation, avg(height) as nation_num from user group by nation with rollup;
+----------+--------------------+
| nation | nation_num |
+----------+--------------------+
| Hui Nationality| 175 |
| Han nationality| 182 |
| Uyghur | 180 |
| Mongolian | 177 |
| NULL | 179.33333333333334 |
+----------+--------------------+
5 rows in set (0.00 sec)

For example, count():

mysql> select nation, count(*) as nation_num from user group by nation with rollup;
+----------+------------+
| nation | nation_num |
+----------+------------+
| Hui Nationality| 1 |
| Han nationality| 2 |
| Uyghur | 2 |
| Mongolian| 1 |
| NULL | 6 |
+----------+------------+
5 rows in set (0.00 sec)

This is the end of this article about the group by in MySQL. For more information about group by in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: