[Mysql query Statement]--Packet Query GROUP BY

#group by
(1) Meaning of GROUP by: Group The results of the query in 1 or more fields, with the same field values as a group
(2) Group by can be used for a single field grouping or for multiple field groupings

Select *  fromemployee;+------+------+--------+------+------+-------------+
|Num|d_id|Name|Age|Sex|Homeaddr|
+------+------+--------+------+------+-------------+
|    1 | 1001 |Tom|    - |Man|Beijinghdq|
|    2 | 1002 |John doe|    - |Woman|Beijingcpq|
|    3 | 1003 |Harry|    - |Man|Changshaylq|
|    4 | 1004 |Aric|    the |Man|England|
+------+------+--------+------+------+-------------+

Select * from the employee  Group by   d_id,sex;     

Select *  fromEmployee Group   bysex;+------+------+--------+------+------+------------+
|Num|d_id|Name|Age|Sex|Homeaddr|
+------+------+--------+------+------+------------+
|    2 | 1002 |John doe|    - |Woman|Beijingcpq|
|    1 | 1001 |Tom|    - |Man|Beijinghdq|
+------+------+--------+------+------+------------+Grouped according to the sex field, the total value of the sex field is only two(' Male ' and ' female '), so it is divided into two groups when group by is used alone, only the first record of each group is shown, so the actual meaning of group by alone is insignificant

#group by + group_concat ()
(1) Group_concat (field name) can be used as an output field,
(2) represents a collection of values for a field in each group, based on the grouping results, using GROUP_CONCAT () after grouping

SelectSex fromEmployeeGroup  bysex;+------+
|Sex|
+------+
|Woman|
|Man|
+------+

SelectSexgroup_concat (name)  fromEmployee Group   bysex;+------+--------------------+
|Sex|Group_concat (name)|
+------+--------------------+
|Woman|John doe|
|Man|Zhang San, Harry, Aric|
+------+--------------------+

SelectSexGroup_concat (d_id)  fromEmployee Group   bysex;+------+--------------------+
|Sex|Group_concat (d_id)|
+------+--------------------+
|Woman| 1002               |
|Man| 1001,1003,1004     |
+------+--------------------+

#group by + aggregate function
(1) Inspired by Group_concat (), since we can count the set of values for a field in each grouping, we can also do something about this "set of values" by using aggregate functions.

SelectSex,group_concat (age) fromEmployeeGroup  bysex;+------+-------------------+
|Sex|Group_concat (age)|
+------+-------------------+
|Woman|  -                |
|Man|  -, -, the          |
+------+-------------------+

Gender statistics were male/the woman's age average .SelectSex avg(age)  fromEmployee Group   bysex;+------+----------+
|Sex| avg(age)|
+------+----------+
|Woman|  24.0000 |
|Man|  22.0000 |
+------+----------+

Gender statistics were male/number of female peopleSelectSex count(Sex)  fromEmployee Group   bysex;+------+------------+
|Sex| Count(Sex)|
+------+------------+
|Woman|          1 |
|Man|          3 |
+------+------------+

#group by + have
(1) Having conditional expressions: used to group queries to specify conditions to output query results
(2) having a function and where, but having it only for group by

SelectSexCount(Sex) fromEmployee Group   bySex   havingCount(Sex)>2;
+------+------------+
|Sex| Count(Sex)|
+------+------------+
|Man|          3 |
+------+------------+

#group by + with rollup
(1) The function of with rollup is to record the sum of all the records in the current column by adding a new line at the end.

SelectSexCount(age) fromEmployee Group   bySex with rollup;+------+------------+
|Sex| Count(age)|
+------+------------+
|Woman|          1 |
|Man|          3 |
| NULL |          4 |
+------+------------+

SelectSex,group_concat (age) fromEmployee Group   bySex with rollup;+------+-------------------+
|Sex|Group_concat (age)|
+------+-------------------+
|Woman|  -                |
|Man|  -, -, the          |
| NULL |  -, -, -, the       |
+------+-------------------+

[Mysql query Statement]--Packet Query GROUP BY