MySQL GROUP BY Statement

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

On the columns used for grouping, we can use functions such as COUNT, SUM, AVG, etc.

GROUP BY syntax

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

Example demonstration

This chapter uses the following table structure and data. We can import the following data into the database before use.

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 'login count',
　　PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--　----------------------------
--　　Records of `employee_tbl`
--　----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', 'Xiao Ming', ''2016-04-22　15:25:33', ''1), ('2', 'Xiao Wang', ''2016-04-20　15:25:47', ''3), ('3', 'Xiao Li', ''2016-04-19　15:26:02', ''2), ('4', 'Xiao Wang', ''2016-04-07　15:26:14', ''4), ('5', 'Xiao Ming', ''2016-04-11　15:26:40', ''4), ('6', 'Xiao Ming', ''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 | date | singin |
+----+--------+---------------------+--------+
|　　1　| Xiao Ming |　2016-04-22　15:25:33　|　　　　　　1　|
|　　2　| Xiao Wang |　2016-04-20　15:25:47　|　　　　　　3　|
|　　3　| Xiao Li |　2016-04-19　15:26:02　|　　　　　　2　|
|　　4　| Xiao Wang |　2016-04-07　15:26:14　|　　　　　　4　|
|　　5　| Xiao Ming |　2016-04-11　15:26:40 |　　　　　　4　|
|　　6　| Xiao Ming |　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(|*) |
+--------+----------+
| Xiao Li |　　　　　　　　1　|
| Xiao Ming |　　　　　　　　3　|
| Xiao Wang |　　　　　　　　2　|
+--------+----------+
3　rows in set (0.01　sec)

Using WITH ROLLUP

WITH ROLLUP can be used to perform the same statistics (SUM, AVG, COUNT, ...) on the basis of grouped statistical data.

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

mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| Xiao Li |　　　　　　　　　　　　2　|
| Xiao Ming |　　　　　　　　　　　　7　|
| Xiao Wang |　　　　　　　　　　　　7　|
| NULL |　　　　　　　　　　　16　|
+--------+--------------+
4　rows in set (0.00 sec)

where NULL records represent the login times of all people.

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 all null, then return null (meaningless).

In the following example, if the name is empty, we use 'Total' instead:

mysql> SELECT coalesce(name, 'Total'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, 'Total') | singin_count |
+--------------------------+--------------+
| Xiao Li |　　　　　　　　　　　　2　|
| Xiao Ming |　　　　　　　　　　　　7　|
| Xiao Wang |　　　　　　　　　　　　7　|
| Total |　　　　　　　　　　　16　|
+--------------------------+--------------+
4　rows in set (0.01　sec)