oracle基本命令集合(3)--分组与统计

SQL> --统计员工总数，最高工资，最低工资，平均工资，工资总和
SQL> 
SQL> select count(*),max(sal),min(sal),avg(sal),sum(sal) from emp;

  COUNT(*)   MAX(SAL)   MIN(SAL)   AVG(SAL)   SUM(SAL)                                                                                      
---------- ---------- ---------- ---------- ----------                                                                                      
        18       5000        800 2201.38889      39625                                                                                      

SQL> --平均奖金
SQL> select sum(comm)/count(*) ,avg(comm) from dual;
select sum(comm)/count(*) ,avg(comm) from dual
                               *
第 1 行出现错误: 
ORA-00904: "COMM": 标识符无效 


SQL> select sum(comm)/count(*) ,avg(comm) from emp;

SUM(COMM)/COUNT(*)  AVG(COMM)                                                                                                               
------------------ ----------                                                                                                               
        122.222222        550                                                                                                               

SQL> --组函数自动滤空
SQL> select count(*),count(deptno),count(comm) from emp;

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

SQL> select * from dept;

    DEPTNO DNAME          LOC                                                                                                               
---------- -------------- -------------                                                                                                     
        10 ACCOUNTING     NEW YORK                                                                                                          
        20 RESEARCH       DALLAS                                                                                                            
        30 SALES          CHICAGO                                                                                                           
        40 OPERATIONS     BOSTON                                                                                                            

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO                                                      
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------                                                      
      8001 李四       MANAGER              2015-10-15       2500                                                                            
      8002 李4        MANAGER              2015-10-16       2500                                                                            
      8003 TOM        MANAGER              2010-10-13       2800                                                                            
      8004 JACK       MANAGER              2016-10-13       2800                                                                            
      7369 SMITH      CLERK           7902 1980-12-17        800                    20                                                      
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30                                                      
      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30                                                      
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20                                                      
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30                                                      
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30                                                      
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10                                                      
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20                                                      
      7839 KING       PRESIDENT            1981-11-17       5000                    10                                                      
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30                                                      
      7876 ADAMS      CLERK           7788 1987-05-23       1100                    20                                                      
      7900 JAMES      CLERK           7698 1981-12-03        950                    30                                                      
      7902 FORD       ANALYST         7566 1981-12-03       3000                    20                                                      

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO                                                      
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------                                                      
      7934 MILLER     CLERK           7782 1982-01-23       1300                    10                                                      

已选择18行。

SQL> select sum(comm)/count(comm),avg(comm) from emp;

SUM(COMM)/COUNT(COMM)  AVG(COMM)                                                                                                            
--------------------- ----------                                                                                                            
                  550        550                                                                                                            

SQL> --分组，按部门统计人数，最低最高工资
SQL> 
SQL> 
SQL> select deptno, count(*),min(sal),max(sal) from emp group by deptno;

    DEPTNO   COUNT(*)   MIN(SAL)   MAX(SAL)                                                                                                 
---------- ---------- ---------- ----------                                                                                                 
                    4       2500       2800                                                                                                 
        30          6        950       2850                                                                                                 
        20          5        800       3000                                                                                                 
        10          3       1300       5000                                                                                                 

SQL> select deptno,empno,ename, count(*),min(sal),max(sal) from emp group by deptno;
select deptno,empno,ename, count(*),min(sal),max(sal) from emp group by deptno
              *
第 1 行出现错误: 
ORA-00979: 不是 GROUP BY 表达式 


SQL> --除了统计函数外，查询字段中只能出现group by中有的列名
SQL> select deptno,job, count(*),min(sal),max(sal) from emp group by deptno,job;

    DEPTNO JOB         COUNT(*)   MIN(SAL)   MAX(SAL)                                                                                       
---------- --------- ---------- ---------- ----------                                                                                       
        20 CLERK              2        800       1100                                                                                       
        30 SALESMAN           4       1250       1600                                                                                       
        20 MANAGER            1       2975       2975                                                                                       
        30 CLERK              1        950        950                                                                                       
        10 PRESIDENT          1       5000       5000                                                                                       
        30 MANAGER            1       2850       2850                                                                                       
        10 CLERK              1       1300       1300                                                                                       
        10 MANAGER            1       2450       2450                                                                                       
        20 ANALYST            2       3000       3000                                                                                       
           MANAGER            4       2500       2800                                                                                       

已选择10行。

SQL> select deptno,job, count(*),min(sal),max(sal) from emp group by deptno,job order by deptno;

    DEPTNO JOB         COUNT(*)   MIN(SAL)   MAX(SAL)                                                                                       
---------- --------- ---------- ---------- ----------                                                                                       
        10 CLERK              1       1300       1300                                                                                       
        10 MANAGER            1       2450       2450                                                                                       
        10 PRESIDENT          1       5000       5000                                                                                       
        20 ANALYST            2       3000       3000                                                                                       
        20 CLERK              2        800       1100                                                                                       
        20 MANAGER            1       2975       2975                                                                                       
        30 CLERK              1        950        950                                                                                       
        30 MANAGER            1       2850       2850                                                                                       
        30 SALESMAN           4       1250       1600                                                                                       
           MANAGER            4       2500       2800                                                                                       

已选择10行。

SQL> select deptno, count(*),min(sal),max(sal),avg(sal) from emp group by deptno;

    DEPTNO   COUNT(*)   MIN(SAL)   MAX(SAL)   AVG(SAL)                                                                                      
---------- ---------- ---------- ---------- ----------                                                                                      
                    4       2500       2800       2650                                                                                      
        30          6        950       2850 1566.66667                                                                                      
        20          5        800       3000       2175                                                                                      
        10          3       1300       5000 2916.66667                                                                                      

SQL> --查询平均工资大于2000的部门的统计信息
SQL> select deptno, count(*),min(sal),max(sal),avg(sal) from emp group by deptno where avg(sal) > 2000;
select deptno, count(*),min(sal),max(sal),avg(sal) from emp group by deptno where avg(sal) > 2000
                                                                            *
第 1 行出现错误: 
ORA-00933: SQL 命令未正确结束 


SQL> --where 中不能使用分组函数
SQL> 
SQL> 
SQL> select deptno, count(*),min(sal),max(sal),avg(sal) from emp group by deptno having avg(sal) > 2000;

    DEPTNO   COUNT(*)   MIN(SAL)   MAX(SAL)   AVG(SAL)                                                                                      
---------- ---------- ---------- ---------- ----------                                                                                      
                    4       2500       2800       2650                                                                                      
        20          5        800       3000       2175                                                                                      
        10          3       1300       5000 2916.66667                                                                                      

SQL> select * from emp having deptno=10;
select * from emp having deptno=10
                         *
第 1 行出现错误: 
ORA-00979: 不是 GROUP BY 表达式 


SQL> spool off