A brief discussion on group by in MySQL

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);

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)

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)

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)

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

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)

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)

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)

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)

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)

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)