The GROUP BY statement groups the result set based on one or more columns.
We can use functions such as COUNT, SUM, AVG, etc. 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 following table structure and data are used in the examples in this chapter. Before using, we can first The following data is imported into the database.
MariaDB [RUNOOB]> SET NAMES utf8;Query OK, 0 rows affected (0.00 sec)
MariaDB [RUNOOB]> SET FOREIGN_KEY_CHECKS = 0;Query OK, 0 rows affected (0.00 sec)
MariaDB [RUNOOB] ]> DROP TABLE IF EXISTS `employee_tbl`;Query OK, 0 rows affected, 1 warning (0.00 sec)MariaDB [RUNOOB]> 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 'Number of logins' ,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.04 sec)
MariaDB [RUNOOB]> BEGIN;Query OK, 0 rows affected (0.00 sec)
MariaDB [RUNOOB]> INSERT INTO employee_tbl VALUES ('1', 'aa', '2016-04-22 15:25:33', '1'), ('2', ' bb', '2016-04-20 15:25:47', '3'), ('3', 'cc', '2016-04-19 15:26:02', '2'), (' 4', 'bb', '2016-04-07 15:26:14', '4'), ('5', 'aa', '2016-04-11 15:26:40', '4' ), ('6', 'aa', '2016-04-04 15:26:54', '2');Query OK, 6 rows affected, 6 warnings (0.00 sec)
Records: 6 Duplicates: 0 Warnings : 6
MariaDB [RUNOOB]> COMMIT;Query OK, 0 rows affected (0.00 sec)
MariaDB [RUNOOB]> SET FOREIGN_KEY_CHECKS = 1;Query OK, 0 rows affected (0.00 sec)
Import successful After that, execute the following SQL statement:
MariaDB [RUNOOB]> select * from employee_tbl;+----+------+----------------- ----+--------+
| id | name | date | singin |
+----+------+------------ ---------+--------+
| 1 | aa | 2016-04-22 15:25:33 | 1 |
| 2 | bb | 2016-04-20 15 :25:47 | 3 |
| 3 | cc | 2016-04-19 15:26:02 | 2 |
| 4 | bb | 2016-04-07 15:26:14 | 4 |
| 5 | aa | 2016-04-11 15:26:40 | 4 |
| 6 | aa | 2016-04-04 15:26:54 | 2 |
+----+------+--- ------------------+--------+
6 rows in set (0.00 sec)
Next we use the GROUP BY statement to set the data table Group by name and count how many records each person has:
MariaDB [RUNOOB]> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;+------+------- ---+
| name | COUNT(*) |
+------+----------+
| aa | 3 |
| bb | 2 |
| cc | 1 |
+------+----------+
3 rows in set (0.00 sec)
Using WITH ROLLUP
WITH ROLLUP can be implemented based on group statistics Same statistics (SUM,AVG,COUNT…).
For example, we group the above data table by name, and then count the number of times each person logs in:
MariaDB [RUNOOB]> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;+-- ----+--------------+
| name | singin_count |
+------+--------------+
| aa | 7 |
| bb | 7 |
| cc | 2 |
| NULL | rows in set (0.00 sec)
The record NULL represents the number of logins 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, select a;
If a==null, select b;
If b==null, select c;
If a b c are both null, return null (meaningless).
In the following examples, if the name is empty, we use the total number instead:
MariaDB [RUNOOB]> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;+------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+------------------------+--------------+
| aa | 7 |
| bb | 7 |
| cc | 2 |
| 总数 | 16 |
+------------------------+--------------+
4 rows in set (0.00 sec)