单表查询（续）

-- 聚合计算
-- 查询emp表中员工总数，最高工资，最低工资、平均工资及工资总和
use test;
select count(*) 员工总数, max(sal) 最高工资, min(sal) 最低工资, avg(sal) 平均工资,
sum(sal) 工资总和 from emp;

select deptno, avg(sal) 平均工资 from emp group by deptno;
select * from emp;

-- 查询各部门不同职位的平均工资
select deptno, job, avg(sal) from emp group by deptno, job
order by avg (sal) desc;

-- 练习：查询各个部门的员工数
select deptno, count(*) from emp group by deptno;
-- 练习：查询各部门不同职位的人数
select deptno, job, count(*) from emp group by deptno, job;
-- 查询各部门不同职位的平均工资
select deptno, job, avg(sal) from emp group by deptno, job;

-- 查询各部门clerk的平均工资
select deptno, job, avg(sal) from emp group by deptno,job 
having job='clerk';

select deptno, job, avg(sal) from emp where job='clerk'
group by deptno;

-- 查询平均工资大于2000的部门
select deptno, avg(sal) from emp group by deptno 
having avg(sal)>2000;