07.聚合函数

聚合函数

sql中主要聚合函数有

count  求数量
max    求最大值
min    求最小值
sum    求和
avg    求平均值

--求员工总人数
select count(*) 总人数 from People
--求员工的最高工资
select max(PeopleSalary) 最高工资 from People
--求员工的最低工资
select min(PeopleSalary) 最低工资 from People 
--求所有员工的工资总和
select sum(PeopleSalary) 工资的和 from People
--求所有员工的平均工资
select round(avg(PeopleSalary),0) 平均工资 from People
--求数量，最大值，最小值，总和，平均值，在一行显示
select count(*) 总人数,max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资,sum(PeopleSalary) 工资的和,
round(avg(PeopleSalary),0) 平均工资 from People
--查询出武汉地区的员工人数，总工资，最高工资，最低工资和平均工资
select count(*) 总人数,max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资,sum(PeopleSalary) 工资的和,
round(avg(PeopleSalary),0) 平均工资 from People
where PeopleAddr='武汉'
--查询出工资比平均工资高的的人员信息
select*from People where PeopleSalary >
(select avg(PeopleSalary) from People)
--求数量，年龄最大值，年龄最小值，年龄总和，年龄平均值
--方案1
select count(*) 数量,max(year(getdate())-year(PeopleBirth)) 最大年龄,
min(year(getdate())-year(PeopleBirth)) 最小年龄, 
sum(year(getdate())-year(PeopleBirth)) 年龄总和,
avg(year(getdate())-year(PeopleBirth)) 平均年龄
from People
--方案2
select count(*) 数量,
max(datediff(year,PeopleBirth,getdate())) 最大年龄,
min(datediff(year,PeopleBirth,getdate())) 最小年龄,
sum(datediff(year,PeopleBirth,getdate())) 年龄总和,
avg(datediff(year,PeopleBirth,getdate())) 平均年龄
from People
--计算出月薪1000以上的男性的最大年龄，最小年龄和平均年龄
select '月薪>1000' 月薪,'男' 性别, 
max(datediff(year,PeopleBirth,getdate())) 最大年龄,
min(datediff(year,PeopleBirth,getdate())) 最小年龄,
avg(datediff(year,PeopleBirth,getdate())) 平均年龄
from People where PeopleSalary>1000 and PeopleSex='男'
--统计出所在地在'武汉或上海'的所有女员工数量及最大年龄，最小年龄和平均工资
select '武汉或上海的女员工' 描述, 
count(*) 数量,
max(datediff(year,PeopleBirth,getdate())) 最大年龄,
min(datediff(year,PeopleBirth,getdate())) 最小年龄,
sum(datediff(year,PeopleBirth,getdate())) 年龄总和,
avg(datediff(year,PeopleBirth,getdate())) 平均年龄
from People where PeopleSex='女' and PeopleAddr in('武汉','北京')
--求出年龄比平均年龄高的人员信息
select*from People where datediff(year,PeopleBirth,getdate()) >
(select avg( year(getdate()) - year(PeopleBirth) ) from People )