聚合函数

--聚合函数
--count 求数量
--max 求最大值
--min 最小值
--求和
--avg 求平均值

select * from People
--求员工总人数
select count() 人数 from People
--求最大值 求最高工资
select max(PeopleSalary) 最高工资 from People
--求最小值 求最小工资
select min(PeopleSalary) 最高工资 from People
--求和 求所有员工的工资总和
select sum(PeopleSalary)最高工资 from People
--求平均值 求所有员工的平均工资
select round (avg(PeopleSalary),2) 平均工资 from People
select round(25.5555,2)
--求数量 最大值 最小值 总和 平均值 在一行显示
select count() 人数,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资,
sum(PeopleSalary)工资总和,round(avg(PeopleSalary),2)平均工资 from People
--查询出武汉地区的员工人数 总工资 最高工资 最低工资和平均工资
select count() 人数,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资,
sum(PeopleSalary)工资总和,round(avg(PeopleSalary),2)平均工资 from People
where PeopleAddress = '武汉'
--求出工资比平均工资高的人员信息
select * from People where PeopleSalary >
(select round(avg(PeopleSalary),2)平均工资 from People)
--求数量 年龄最大值 年龄最小值 年龄总和 年龄平均值 在一行显示
select,year(getdate())-year(PeopleBirth) from People
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 DATEDIFF(year,'1991-1-1','1993-3-3')
select count(*)数量,
max(DATEDIFF(year,PeopleBirth,GETDATE()))最高年龄,
min(DATEDIFF(year,PeopleBirth,GETDATE()))最低年龄,
sum(DATEDIFF(year,PeopleBirth,GETDATE()))年龄综合,
avg(DATEDIFF(year,PeopleBirth,GETDATE()))平均年龄
from People

select '月薪10000以上' 月薪,'男'性别,
count(*)数量,
max(DATEDIFF(year,PeopleBirth,GETDATE()))最高年龄,
min(DATEDIFF(year,PeopleBirth,GETDATE()))最低年龄,
sum(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 PeopleAddress in('北京','上海')

--求出年龄比平均年龄高的人员信息
select * from People where year(getdate())-year(PeopleBirth)>
(select avg(year(getdate())-year(PeopleBirth))from People)