SQL sever数据库----聚合函数

----聚合函数------------------------------
 --(1)员工总人数
 select count(*) 人数 from People
 --(2)最大工资
 select max(PeopleSalary) 最大工资 from People
 --(3)最小工资
 select min(PeopleSalary) 最小工资 from People
 --(4)所有员工的工资总和
 select sum(PeopleSalary) 工资总和 from People
 --(5)所有员工的平均工资
 select round(avg(PeopleSalary),2)平均工资 from People
 --(6)求数量,最大值,最小值,总会,平均值,在一行显示
 select count(*) 人数,max(PeopleSalary) 最大工资,min(PeopleSalary) 最小工资,sum(PeopleSalary) 工资总和,round(avg(PeopleSalary),2)平均工资
 from People
 --(7)武汉地区的员工人数,总工资,最高工资,最低工资和平均工资
 select count(*) 人数,max(PeopleSalary) 最大工资,min(PeopleSalary) 最小工资,sum(PeopleSalary) 工资总和,round(avg(PeopleSalary),2)平均工资
 from People
where PeopleAddress='武汉'
--(8)求工资比平均工资高的人员信息
select * from People
where PeopleSalary>(select round(avg(PeopleSalary),2)平均工资 from People)
--(9)求数量,年龄最大值,年龄最小值,年龄总和,年龄平均值,在一行表示
--1
--select *,year(getdate())-year(PeopleBirth) from people
select count(*) 人数,
max(year(getdate())-year(PeopleBirth)) 年龄最大值,
min(year(getdate())-year(PeopleBirth)) 年龄最小值,
sum(year(getdate())-year(PeopleBirth)) 年龄总和,
round(avg(year(getdate())-year(PeopleBirth)),2)平均年龄
 from People
--2
--select datediff(year,PeopleBirth,getdate()) 
--from People
select count(*) 人数,
max(datediff(year,PeopleBirth,getdate()) ) 年龄最大值,
min(datediff(year,PeopleBirth,getdate()) ) 年龄最小值,
sum(datediff(year,PeopleBirth,getdate()) ) 年龄总和,
round(avg(datediff(year,PeopleBirth,getdate()) ),2)平均年龄
 from People
 --(10)计算月薪在10000以上的男性员工的最大年龄,最小年龄和平均年龄
 select max(year(getdate())-year(PeopleBirth)) 年龄最大值,
 min(year(getdate())-year(PeopleBirth)) 年龄最小值,
 round(avg(year(getdate())-year(PeopleBirth)),2)平均年龄
 from People
 where PeopleSalary>10000 and PeopleSex='男'
 --(11)统计"武汉"或"上海"地区所有女员工的数量以及最大年龄,最小年龄,平均年龄
 select '武汉或上海女员工' 描述,count(*) 人数,
max(year(getdate())-year(PeopleBirth)) 年龄最大值,
min(year(getdate())-year(PeopleBirth)) 年龄最小值,
round(avg(year(getdate())-year(PeopleBirth)),2)平均年龄
 from People
 where (PeopleAddress in('武汉','上海')) and PeopleSex='女'
 --(12)年龄比平均年龄高的员工信息
 select * from People
 where (year(getdate())-year(PeopleBirth))
 >(select avg(year(getdate())-year(PeopleBirth)) from People)