Oracle 多行函数

来源：互联网发布：51信用卡待遇知乎编辑：程序博客网时间：2024/06/06 00:31

工资总额

  select sum(sal) from emp;

SUM(SAL)   ----------      29025

人数

    select count(*) from emp;

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

平均工资

  select sum(sal)/count(*) 一,avg(sal) 二 from emp;

        一         二    ---------- ----------    2073.21429 2073.21429

平均奖金

  select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三  from emp;

        一         二         三     ---------- ---------- ----------     157.142857        550        550

  select count(*),count(comm) from emp;

  COUNT(*) COUNT(COMM)    ---------- -----------            14           4

多个列的分组

  select deptno,job,sum(sal)  from emp  group by deptno,job  order by 1;

    DEPTNO JOB         SUM(SAL)     ---------- --------- ----------             10 CLERK           1300             10 MANAGER         2450             10 PRESIDENT       5000             20 ANALYST         6000

过滤分组数据

–查询平均工资大于2000的部门

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

    DEPTNO   AVG(SAL)     ---------- ----------             20       2175             10 2916.66667

where和having最大的区别：where后面不能使用组函数

–查询10号部门的平均工资

  select deptno,avg(sal)  from emp  group by deptno  having deptno=10;

    DEPTNO   AVG(SAL)   ---------- ----------           10 2916.66667

group by的增强

select deptno,job,sum(sal) from emp group by deptno,job+select deptno,sum(sal) from emp group by deptno+select sum(sal) from emp==========================select deptno,job,sum(sal) from emp group by rollup(deptno,job);group by rollup(a,b)=group by a,b+group by a+group by null

———————————————-

  select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)    ---------- --------- ----------            10 CLERK           1300            10 MANAGER         2450            10 PRESIDENT       5000            10                 8750            20 CLERK           1900            20 ANALYST         6000            20 MANAGER         2975            20                10875            30 CLERK            950            30 MANAGER         2850            30 SALESMAN        5600

0 0