sql server 聚合函数

--聚合函数
--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 avg(PeopleSalary) 平均工资 from People
select round(25.5555,2)

select count(*)人数 ,max(PeopleSalary)最高工资 ,min(PeopleSalary)最低工资,sum(PeopleSalary)工资之和,avg(PeopleSalary) 平均工资 from People 

select count(*) 人数  ,max(PeopleSalary) 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 
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 count(*) 数量,
max(year(getdate())-year(PeopleBirth)) 最高年龄,
min(year(getdate())-year(PeopleBirth)) 最低年龄,
sum(year(getdate())-year(PeopleBirth)) 年龄总和,
avg(year(getdate())-year(PeopleBirth)) 平均年龄
from People where PeopleSex='男'and PeopleAddress='北京'