MySQL Group (v)

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)operator Valuegroup by column_name;  

Example Demo

This chapter example uses the following table structure and data, we can first import the following data into the database before use.

SET NAMES UTF8;SET Foreign_key_checks= 0;-- ------------------------------ TableStructureFor ' 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=InnoDBDEFAULT CHARSET=Utf8;-- ------------------------------ RecordsOf' 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 ', Xiaoli, ' 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 ' ); ; SET foreign_key_checks = 1    

After the import succeeds, execute the following SQL statement:

Mysql> SetNames 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 | Xiaoli | 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 |  Xiaoming  |2016-04-04  15:26: 54 |2 |+----+--------+---------------------+--------+6in 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(*) |+--------+----------+| Xiaoli | 1 ||   Xiaoming  |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) AsSingin_count from Employee_tbl GROUP by name with ROLLUP;+--------+--------------+|Name|Singin_count|+--------+--------------+| Xiaoli | 2 || Xiao ming |  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),SUM(Singin) AsSingin_count from Employee_tbl GROUP by name with ROLLUP;+--------------------------+--------------+|Coalesce(Name, Total) |Singin_count|+--------------------------+--------------+| Xiaoli | 2 || Xiao ming | 7 ||   Xiao Wang  |7 ||   Total  |16 |+--------------------------+--------------+4in set  (0.01 Sec)          

MySQL Group (v)