SQL server数据库----分组查询

----分组查询--------------------------------
 --(1)根据所在地区分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资
 select PeopleAddress 地区,
		count(*) 员工人数,
		sum(PeopleSalary) 工资总和,
		round(avg(PeopleSalary),2) 平均工资,
		max(PeopleSalary) 最高工资,
		min(PeopleSalary) 最低工资
		from People
group by PeopleAddress
--(2)根据员工所在地区分组统计员工人数,员工工资总和,平均工资,最高工资,最低工资,
--1985年及以后出生的员工不参与统计
select PeopleAddress 地区,
		count(*) 员工人数,
		sum(PeopleSalary) 工资总和,
		round(avg(PeopleSalary),2) 平均工资,
		max(PeopleSalary) 最高工资,
		min(PeopleSalary) 最低工资
		from People
where year(PeopleBirth)<1985
group by PeopleAddress
--(3)根据员工所在地区分组统计员工人数,员工工资总和,平均工资,最高工资,最低工资,
--要求筛选出员工人数至少在2人及以上的记录,1985年及以后出生的员工不参与统计
select PeopleAddress 地区,
		count(*) 员工人数,
		sum(PeopleSalary) 工资总和,
		round(avg(PeopleSalary),2) 平均工资,
		max(PeopleSalary) 最高工资,
		min(PeopleSalary) 最低工资
		from People
where year(PeopleBirth)<1985 
group by PeopleAddress
having  count(*) >=2