Oracle起步学习（七）--组函数

分组函数

通过本章学习，您将可以:

l了解组函数。

l描述组函数的用途。

l使用GROUPBY 子句数据分组。

l使用HAVING 子句过滤分组结果集

一，组函数：AVG、COUNT、MAX、MIN、SUM

语法：

SELECT  [column,] group_function(column), ...

FROM  table

[WHERE  condition]

[GROUP BY  column]

[ORDER BY  column];

1.工资总额：求和函数sum

select sum(sal) from emp;

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

2.总人数：计数函数count

select count(*) from emp;

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

3.平均工资：平均值函数avg，组函数 忽略空值

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

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

这里结果是一样的， 一、二都是计算平均工资

4.平均奖金: 空值 5

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

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

count(*) ：总行数

count(comm)：有薪水的个数

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

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

这里可以使用 nvl与组函数使用。

5.在组函数中使用NVL函数： NVL函数使分组函数无法忽略空值

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

  COUNT(*) COUNT(NVL(COMM,0))                                                   
---------- ------------------                                                   
        14                 14    

二、分组数据

SELECT  column, group_function(column)

FROM  table

[WHERE  condition]

[GROUP BY  group_by_expression]

[ORDER BY  column];

可以使用GROUPBY子句将表中的数据分成若干组

1.求每个部门的平均工资

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

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

2.先按照部门分组，再按照工作分组。

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                                                 
        20 CLERK           1900                                                 
        20 MANAGER         2975                                                 
        30 CLERK            950                                                 
        30 MANAGER         2850                                                 
        30 SALESMAN        5600                                                

已选择9行。

SELECT  a,b,...n, group_function(column)

FROM  table

[GROUP BY   a,b,...n ]

[ORDER BY  column];

没有在组函数中的 列 （a,b,...n）必须 出现在 GROUP BY 后（GROUP BY  a,b,...n）

但是反过来并不一定了。

3.过滤分组：HAVING子句

使用HAVING 过滤分组:

①.  行已经被分组。

②.  使用了组函数。

③.  满足HAVING 子句中条件的分组将被显示。

eg:求10号部门的平均工资

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

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

注意：HAVING 与 WHERE 功能相当。

区别：HAVING后可以带有分组函数的，WHERE后 不可以

即可以使用WHERE的地方，HAVING都可以；但是，能用WHERE的地方尽肯能使用WHERE。

4.group by 增强： rollup(a,b,c)

group by rollup(a,b)

<==>

group by a,b

+

group by a

+

group by null

eg:

break on deptno skip 2
select deptno,job,sum(sal) from emp group by rollup(deptno,job);

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

    DEPTNO JOB         SUM(SAL)                                                 
---------- --------- ----------                                                 
                                                                                
        30 CLERK            950                                                 
           MANAGER         2850                                                 
           SALESMAN        5600                                                 
                           9400                                                 
                                                                                
                                                                                
                          29025                                                 
                                                                                
                                                                                

已选择13行。

break on null 

注： break on cols :一下每列出现重复只显示一次