MySQL GROUP by statement

MySQL GROUP by statement

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

On the grouped columns we can use COUNT, SUM, AVG, and so on.

GROUP by syntax

SELECT column_name, Function (column_name) from Table_namewhere column_name operator Valuegroup by column_name;

Example Demo

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 (one)  NOT NULL,   ' name ' '  char  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 ',  ' xiaoming ',  ' 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 ',  ' xiaoming ',  ' 2016-04-11 15:26:40 ',  ' 4 '),  (' 6 ',  ' xiaoming ',  ' 2016-04-04  15:26:54 ',  ' 2 '); COMMIT; set foreign_key_checks = 1;

After the import succeeds, execute the following SQL statement:

mysql> set names utf8;mysql> select * from employee_tbl;+----+------- -+---------------------+--------+| id | name   | date                 | singin |+----+------ --+---------------------+--------+|  1 |  Xiao Ming  | 2016-04-22 15:25:33 |       1 | |   2 |  Xiao Wang  | 2016-04-20 15:25:47 |      3  | |   3 |  Little 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 implemented on the basis of grouping statistics and then the same statistics (Sum,avg,count ... ）。

For example, we group the above data tables by name, and then count the number of times each person logs in:

Mysql> select name, sum (Singin)  as singin_count from  employee_tbl  group by name with rollup;+--------+--------------+| name   |  singin_count |+--------+--------------+|   |             2 | |   Xiaoming  |            7 | |   Xiao Wang  |            7 | |  null   |           16 |+----- ---+--------------+4 rows in set  (0.00 sec)

Where the record NULL indicates 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 B; If b==null, select C; If A!=null, select A; if a b c is null, the return is null (meaningless).

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

Mysql> select coalesce (name,  ' total number '),  sum (Singin)  as singin_count from   employee_tbl group by name with rollup;+--------------------------+------ --------+| coalesce (name,  ' total ')  | singin_count |+--------------------------+--------- -----+|                      |            2 | |   Xiao Ming                     |            7 | |   Xiao Wang                     |            7 | |   Total                    |            16 |+--------------------------+--------------+4 rows in set  (0.01 SEC)

MySQL GROUP by statement