分组函数
# sum avg max min count(计算个数)
#round( ,n) 小数保留两位
select sum(id) from user_detail_info;
select avg(id) from user_detail_info;
select max(id) from user_detail_info;
select count(gender) from user_detail_info;
select min(create_time) from user_detail_info;

和 distinct搭配实现去重
select count(distinct english_level) from user_detail_info;
select count( english_level) from user_detail_info;

count函数的单独介绍
select count(*) fromuser_detail_info;#总行数
select count(1) from user_detail_info;#统计1的个数 或者常量值也是行数
#和分组函数一同查询的字段有限制
#分组查询每个什么 就groupby 什么
# 查询每个部门的平均工资把所有人 分成不同组

select fromwhere group by order by **

select max(create_time),english_level from user_detail_info group by english_level;
#查询 每个位置上的部门个数
select count(*) ,gender from user_detail_info group by gender;

添加筛选条件
#查询 性别是男的 每个level的平均id
select avg(id) , english_level from user_detail_info where gender=’MALE’ group by english_level;

添加筛选条件
#查询 哪个level id个数大于200
# 1、 查询每个level的id个数 2、 根据1的结果筛选
SELECT
COUNT(id), english_level
FROM
user_detail_info
GROUP BY english_level
HAVING COUNT(id) > 100;

按表达式或函数分组
# 按用户姓名长度分组 ，查询每一组的用户id个数 筛选个数>5的有哪些
# 1、查询每个长度的员工个数
SELECT
COUNT(id), LENGTH(user_name)
FROM
user_info
GROUP BY LENGTH(user_name)
HAVING COUNT(id) > 5;

2、添加筛选条件
#按多个字段分组
#查询每个level 每个gender的平均id
SELECT
AVG(id), english_level, gender
FROM
user_detail_info
GROUP BY english_level , gender;

分组查询 添加排序

查询每个level 每个gender的平均id并且按平均id的高低显示
SELECT
AVG(id), english_level, gender
FROM
user_detail_info
WHERE
gender <> ‘MALE’
GROUP BY english_level , gender
HAVING AVG(id) > 100
ORDER BY AVG(id) DESC;