MySQL grouping function

The group function is for a non-null value for the specified field. NOTE: Group functions cannot appear in the WHERE clause!!!

AVG ()average (only for numeric type)Max ()Maximum value (unrestricted type)Min ()Minimum value (unrestricted type)Count ()count (number of records, number of rows)StdDev ()variance (only for numeric type)SUM ()sum (only for numeric type) Prerequisites: Person Table

2. id namedept_idsalarymanager_id
4. 0001wangda101 8500[NULL]
6. 0002wanger1033 000 0009
8. 0003wangsan102 4000 0006
10. 0004wangsi104 2000 0005
12. 0005wangwu104 6000 0001
14. 0006wangliu102 7000 0001
16. 0007wangqi105 5000 0008
18. 0008wangba105 5500 0001
20. 0009wangjiu103 6000 0001
22. 0010wangshi104 900 0005
24. 0011wangsy103 5000 0009
26. 0012wangse[NULL]3000[NULL]

Group by sort (can have one or more fields) function: Check the average salary of each department according to department

2. SELECT
4. dept_id,
6. avg(salary)
8. FROM
10. person
12. GROUP BY
14. dept_id;

Results:

2. dept_idavg(salary)
4. [NULL]3000.000000
6. 101 8500.000000
8. 102 5500.000000
10. 103 4666.666667
12. 104 2966.666667
14. 105 5250.000000

HavingWhen you include a group function in a query condition, it is used instead of where (note: In MySQL, the having can only be placed behind GROUP by!) )function: The Department name (Dept table), Department average salary (person table, Group function) is queried for department average salary above 5000

2. SELECT
4. p.dept_id,
6. dept_name,
8. avg(salary)
10. FROM
12. person p,dept d
14. where p.dept_id = d.dept_id
16. GROUP BY
18. p.dept_id
20. HAVING
22. avg(salary)>5000;

Results:

1. dept_iddept_nameavg(salary)
2. 101 zongwu 8500.000000
3. 102 zhenggong5500.000000
4. 105 renshi 5250.000000

MySQL grouping function