组函数的使用小结

1、常用的组函数：

avg（expr）：表示计算表达式的平均值；

sum(expr)：表示计算表达式的总和；

max(expr)：表示计算表达式的最大值：

min（expr）：表示计算表达式的最小值；

count(*|expr)：表示计算表中数据的总数；

实验一下：

SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected;

avg函数：

SQL> select avg(sal),avg(comm) from emp;

  AVG(SAL)  AVG(COMM)
---------- ----------
2073.21429        550

avg(comm)函数中计算的是四个值的平均值也就是说是计算的是确定值的平均值。要想计算全部数据的平均值，要用到nvl函数：

SQL> select avg(sal),avg(nvl(comm,0)) from emp;

  AVG(SAL) AVG(NVL(COMM,0))
---------- ----------------
2073.21429       157.142857
计算员工的总数：

SQL> select count(empno),count(*) from emp;

COUNT(EMPNO)   COUNT(*)
------------ ----------
          14         14

说明sql语言的编译顺序（from->where->group by->having->select->order by）

1、select之后，from之前出现的字段，要么被用于组函数，要么被用于group by之后（被用于group by 之后 的也可以没有出现在select之后）

2、group  by 要用在where之后

3、group  by 中不能用列别名

where子句对分组前的数据筛选而having则对分组后的子句筛选（where子句中不能用组函数，having子句中可以用组函数）

SQL> select deptno,sum(sal),count(*),avg(sal)
  2  from emp
  3  where avg(sal)<2500
  4  having avg(sal)<2500
  5  order by deptno;
where avg(sal)<2500
      *
ERROR at line 3:
ORA-00934: group function is not allowed here
出错的原因如上。

函数的嵌套：

求平均工资最大的部门名字

SQL> select dname
  2  from dept
  3  where deptno = ( select deptno
  4  from emp
  5   having avg(sal) =(select max(avg_sal) from (select avg(sal) avg_sal from emp
  6  group by deptno))
  7   group by deptno);

DNAME
--------------
ACCOUNTING

其中要用到多个函数嵌套。