概念:
输入参数N个,输出一个
--举例说明;
SQL> select sum(sal) 工资之和 ,avg(sal) 平均工资,min(sal) 最少工资,max(sal) 最多工资 from emp;
工资之和 平均工资 最少工资 最多工资
---------- ---------- ---------- ----------
41725 2781.66667 1200 7000
--求各个部门的平均工资;
SQL> select avg(sal),deptno from emp group by deptno;
AVG(SAL) DEPTNO
---------- ----------
2270 30
2662.5 20
1650 40
4250 10
--各个工种的平均工资
SQL> select round(avg(sal)),job from emp group by job;
ROUND(AVG(SAL)) JOB
--------------- ---------
1530 CLERK
1800 SALESMAN
7000 PRESIDENT
4358 MANAGER
3400 ANALYST
--各个部门各个工种的平均工资
SQL> select avg(sal),deptno,job from emp group by deptno,job;
AVG(SAL) DEPTNO JOB
---------- ---------- ---------
1533.33333 20 CLERK
1850 30 SALESMAN
4575 20 MANAGER
1350 30 CLERK
7000 10 PRESIDENT
1650 40 SALESMAN
4450 30 MANAGER
1700 10 CLERK
4050 10 MANAGER
3400 20 ANALYST
--统计表格中的记录数
SQL> select count(*) from emp where deptno=20;
COUNT(*)
----------
6