Oracle_组函数

工资总额
select sum(sal) from emp;

员工人数
select count(*) from emp;
                                                                   
平均工资
select sum(sal)/count(*) 一, avg(sal) 二 from emp;                                                       
平均奖金
select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp;                                          
空值 4. 组函数会自动滤空（重点）
select count(*),count(comm) from emp;
select count(*),count(nvl(comm,0)) from emp;                                              
可以在组函数中嵌套滤空函数，来屏蔽组函数的滤空功能

求各个部门的平均工资
select deptno,avg(sal) from emp group by deptno                                                                                                  
语法
select deptno,job,avg(sal) from emp group by deptno（出错）
select deptno,job,avg(sal) from emp group by deptno,job（正确）
--group by多列：先按照第一列分；如果第一列相同，再按照第二列分

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

按部门，不同的职位统计平均工资

--having
--查询平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000

--having 过滤分组
--having和where的区别
--求10号部门的平均工资
select deptno,avg(sal) from emp group by deptno having deptno=10;

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

--SQL优化  3. 尽量使用where
              例外：如果条件中含有组函数，只能使用having

--group by的增强
 group by deptno,job
 +
 group by deptno
 +
 group by null
 相当于  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-------->group by deptno,job的作用
        10 PRESIDENT      5000-------/
        10                              8750-----------> group by deptno的作用
        20 CLERK               1900         ^
        20 ANALYST           6000         |
        20 MANAGER         2975         |
        20                             10875-----|
        30 CLERK               950          |
        30 MANAGER         2850        |
        30 SALESMAN       5600        |
        30                             9400------
                                         29025---------->group by null的作用

--SQLPLUS支持报表功能
 break on deptno skip 2
 select deptno,job,sum(sal)
  from emp
  group by rollup(deptno,job);

 break on null
得到结果：
    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK             1300
           MANAGER           2450
           PRESIDENT       5000
                                         8750  

                                                    ---->这里会空2行 "skip 2" 的作用

        20 CLERK              1900
             ANALYST           6000
             MANAGER         2975
                                         10875

        30 CLERK              950
             MANAGER         2850
             SALESMAN       5600
                                         9400

                                         29025