6. Aggregate function and grouping statistics

1. Inquiry Department 30 The total number of people

select count(empno)  The total number of  
from emp 
where deptno=30;

2. Inquiry Department 30 The highest wage of 、 minimum wage 、 Average wage

select max(sal),min(sal),round(avg(sal),2)
from emp
where deptno=30;

3. Query the average salary of each department

select deptno  Department number ,avg(sal)  Average wage 
from emp 
group by deptno;

select d.dname  Department name ,avg(sal)  Average wage 
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;

4. Query the name of the Department and the number of employees in each department

select d.dname  Department name ,count(e.empno)  Number of employees 
from dept d 
left join emp e on d.deptno=e.deptno
group by d.dname;

5. Query the average wage greater than 2000 The department number and average salary of

select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;

6. Find the position name of non salesperson , And the total monthly salary of employees engaged in the same work , And the total salary shall be greater than 5000, The query results are arranged in ascending order of the sum of monthly wages

select job,sum(sal) sum
from emp
where job!='saleman'
group by job
having sum(sal)>5000
order by sum;