[Mysql query Statement]--Packet Query GROUP BY

• #group by
• #group by + group_concat ()
• #group by + aggregate function
• #group by + have

#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|26| Man| Beijinghdq||2|1002| John doe|24| Woman| Beijingcpq||3|1003| Harry|25| Man| Changshaylq||4|1004| Aric|15| Man| England|+------+------+--------+------+------+-------------+
Select*From employeeGroup by D_id,sex;
Select*From employeegroup   bySex+------+------+--------+------+------+------------+| Num| d_id| Name| Age| Sex| Homeaddr|+------+------+--------+------+------+------------+|2|1002| John doe| 24 | female  | BEIJINGCPQ || 1 | 1001 | Zhang San | 26 | male | beijinghdq | +------+------+--------+ ------+------+------------+ grouped according to the sex field, the total value of the sex field is only two  (' Male ' and ' female '     

#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

Select SexFrom employeeGroupBySex+------+| Sex|+------+| Woman|| Man|+------+Select Sex,group_concat (name) From employeegroup   bySex+------+--------------------+| Sex| Group_concat (name)|+------+--------------------+| Woman| John doe|| Man| Zhang San, Harry, Aric|+------+--------------------+Select Sex,Group_concat (d_id) From employeegroup   bySex+------+--------------------+ | Sex | group_concat (d_id) | +------+--------------------+ | Female | 1002 || Male | 1001,1003,< Span style= "color: #800000; Font-weight:bold ">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.

Select Sex,group_concat (age)From employeeGroupBySex+------+-------------------+| Sex| Group_concat (age)|+------+-------------------+| Woman|24|| Man|26,25,15|+------+-------------------+
Gender statistics were male/The woman's age average.Select Sex,avg (age) From employeegroup   bySex+------+----------+| Sex|AVG (age)|+------+----------+| Woman|24.0000|| Man|22.0000|+------+----------+
Gender statistics were male/Number of female peopleSelect Sex,count (Sex) From employeegroup   bysex; +------+------------+| sex | count (Sex) |  +------+------------+| women | 1 || men | 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

Select Sex,COUNT (Sex)From employeegroup   bySex havingcount (Sex)>2;  +------+------------+| sex | count (Sex) |  +------+------------+| men | 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.

Select Sex,Count (age)From employeegroup   bySex withRollup;+------+------------+| Sex|Count (age)|+------+------------+| Woman|1|| Man|3||Null|4|+------+------------+Select Sex,group_concat (age)From employeegroup   bySex withRollup;+------+-------------------+| Sex| Group_concat (age)|+------+-------------------+| female | 24 || Male | 26,25,15 || null | 24,26,25,15 |+------+-------------------+  

[Mysql query Statement]--Packet Query GROUP BY