Oracle Learning Article Five: Group functions, grouping statistics

Common group functions:
1.ccount () to find out the total number of records.
2.max () to find out a set of maximum values
3.min () to find a minimum set of values
4.avg () to find the average
5.sum () summation

--1. Statistics of Employees:
Select COUNT (empno) from EMP;

--2. Find out the most high-paying capital
Select Max (SAL) from EMP;

--3. Finding the minimum wage
Select min (sal) from EMP;

--4. Finding the average salary
Select AVG (SAL) from EMP;

--Query the number of employees in each department
Select Deptno,count (empno) from the EMP Group by Deptno

--Query the average salary of each department
Select Deptno,avg (SAL) from the EMP Group by Deptno
Note: Grouping functions can only be used alone, or you can specify grouping criteria, which allows you to query the grouping criteria.
Select Empno,count (SAL) from EMP;---cannot execute

Select Deptno,avg (SAL) from EMP where AVG (SAL) >2000 GROUP by deptno--cannot execute
----NOTE: The grouping function cannot be used as a condition in hwere and can only be used in the having.
Select Deptno,avg (SAL) from the EMP GROUP by DEPTNO have avg (SAL) >2000

Select Max (SAL) from the EMP GROUP by deptno;--nested grouping functions can only be used separately

--Query the job name of the non-salesperson, and the sum of the salary of the person who is engaged in the same job, and to meet the sum of the work performed on the same job is greater than 5000, arrange the payroll total ascending.
Select Job, sum (SAL) Su from emp where Job <> ' salesman ' GROUP by job have sum (SAL) >5000 order by Su;

Oracle Learning Article Five: Group functions, grouping statistics