MySQL grouping

MySQL GROUP BY statement

The GROUP BY statement groups the result set based on one or more columns.

We can use COUNT, SUM, AVG, and other functions on grouped columns.

GROUP BY syntax

SELECT column_name, function(column_name)FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Example demonstration

The example in this chapter uses the following table structure and data. Before using it, we You can first import the following data into the database.

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ------------------------------ 
 Table structure for `employee_tbl`
 -- ----------------------------
 DROP TABLE IF EXISTS `employee_tbl`;CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  -- ------------------------------  
  Records of `employee_tbl`
  -- ----------------------------
  BEGIN;INSERT INTO `employee_tbl` VALUES 
  ('1', '小明', '2016-04-22 15:25:33', '1'), 
  ('2', '小王', '2016-04-20 15:25:47', '3'), 
  ('3', '小丽', '2016-04-19 15:26:02', '2'), 
  ('4', '小王', '2016-04-07 15:26:14', '4'),
  ('5', '小明', '2016-04-11 15:26:40', '4'), (
   '6', '小明', '2016-04-04 15:26:54', '2');
   COMMIT;SET FOREIGN_KEY_CHECKS = 1;

After the import is successful, execute the following SQL statement:

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

Next we use the GROUP BY statement to group the data table by name and count how many records each person has:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

Use WITH ROLLUP

WITH ROLLUP can implement the same statistics (SUM, AVG, COUNT...) based on grouped statistical data.

For example, we group the above data table by name, and then count the number of login times for each person:

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

The record NULL indicates the number of login times for everyone.

We can use coalesce to set a name that can replace NUll. coalesce syntax:

select coalesce(a,b,c);

Parameter description:

If a==null, Then select b; if b==null, then select c; if a!=null, then select a; if a b c are both null, the return is null (meaningless).

In the following examples, if the name is empty, we use the total number instead:

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+| 
coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)

Related video tutorial recommendations:

MySQL grouping

php.cn