《卸甲笔记》-分组统计查询对比之二

13在分组查询的SELECT子句中出现其他字段（ename）
Oracle

SQL> select deptno,ename,COUNT(empno)
  2  from emp
  3  GROUP  BY deptno;
select deptno,ename,COUNT(empno)
              *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

PPAS

scott=# select deptno,ename,COUNT(empno)
scott-# from emp
scott-# GROUP  BY deptno;
ERROR:  column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select deptno,ename,COUNT(empno)

14在SELECT子句之后只出现分组字段和统计函数
Oracle

SQL> select deptno,COUNT(empno)
  2  from emp
  3  GROUP BY deptno;

    DEPTNO COUNT(EMPNO)
---------- ------------
    30        6
    20        5
    10        3

PPAS

scott=# select deptno,COUNT(empno)
scott-# from emp
scott-# GROUP BY deptno;
 deptno | count
--------+-------
     20 |     5
     30 |     6
     10 |     3
(3 rows)

15求出每个部门平均工资最高的工资
Oracle

SQL> select MAX(AVG(sal)) from emp GROUP BY deptno;

MAX(AVG(SAL))
-------------
   2916.66667

PPAS

PPAS不支持聚合函数嵌套
scott=# select MAX(AVG(sal)) from emp GROUP BY deptno;
ERROR:  aggregate function calls cannot be nested
LINE 1: select MAX(AVG(sal)) from emp GROUP BY deptno;

16错误的语句
Oracle

SQL> select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;
select deptno,MAX(AVG(sal)) from emp GROUP BY deptno
       *
ERROR at line 1:
ORA-00937: not a single-group group function

PPAS

scott=# select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;
ERROR:  aggregate function calls cannot be nested
LINE 1: select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;

17统计函数嵌套分析
Oracle

SQL> select deptno,SUM(sal) from emp GROUP BY deptno;

    DEPTNO   SUM(SAL)
---------- ----------
    30   9400
    20  10875
    10   8750

PPAS

scott=# select deptno,SUM(sal) from emp GROUP BY deptno;
 deptno |   sum
--------+----------
     20 | 10875.00
     30 |  9400.00
     10 |  8750.00
(3 rows)

18查询每个部门的名称、部门人数、部门平均工资、平均服务年限
Oracle

SQL> select d.dname,COUNT(e.empno),ROUND(AVG(e.sal),2) avgsal,
  2  ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12),2) avgyear
  3   from dept d,emp e
  4  where e.deptno(+)=d.deptno
  5  GROUP BY d.dname;

DNAME       COUNT(E.EMPNO)     AVGSAL    AVGYEAR
--------------- -------------- ---------- ----------
ACCOUNTING           3    2916.67      34.69
OPERATIONS           0
RESEARCH             5       2175      32.71
SALES                6    1566.67      34.99

PPAS

scott=# select d.dname,COUNT(e.empno),ROUND(AVG(e.sal),2) avgsal,
scott-# ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12),2) avgyear
scott-# from dept d,emp e
scott-# where e.deptno(+)=d.deptno
scott-# GROUP BY d.dname;
   dname    | count | avgsal  | avgyear
------------+-------+---------+---------
 ACCOUNTING |     3 | 2916.67 |   34.69
 RESEARCH   |     5 | 2175.00 |   32.71
 OPERATIONS |     0 |         |
 SALES      |     6 | 1566.67 |   34.98
(4 rows)

19查询公司各个工资等级雇员的数量、平均工资
Oracle

SQL> select s.grade,COUNT(e.empno),ROUND(AVG(e.sal),2)
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal
  4  GROUP BY s.grade;

     GRADE COUNT(E.EMPNO) ROUND(AVG(E.SAL),2)
---------- -------------- -------------------
     1      3         950
     2      3         1266.67
     4      5        2855
     5      1        5000
     3      2        1550

PPAS

scott=# select s.grade,COUNT(e.empno),ROUND(AVG(e.sal),2)
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal
scott-# GROUP BY s.grade;
 grade | count |  round
-------+-------+---------
     5 |     1 | 5000.00
     1 |     3 |  950.00
     3 |     2 | 1550.00
     4 |     5 | 2855.00
     2 |     3 | 1266.67
(5 rows)

20统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限，雇员人数
Oracle

SQL> select '不领取佣金',ROUND(AVG(sal),2) avgsal,
  2  ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
  3  COUNT(empno) count
  4  from emp
  5  where comm IS NOT NULL
  6  UNION
  7  select '领取佣金',ROUND(AVG(sal),2) avgsal,
  8  ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
  9  COUNT(empno) count
 10  from emp
 11  where comm IS NULL;

'不领取佣金'              AVGSAL    AVGYEAR      COUNT
------------------------------ ---------- ---------- ----------
不领取佣金              1400      35.05          4
领取佣金               2342.5      33.73         10

PPAS

scott=# select '不领取佣金',ROUND(AVG(sal),2) avgsal,
scott-# ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
scott-# COUNT(empno) count
scott-# from emp
scott-# where comm IS NOT NULL
scott-# UNION
scott-# select '领取佣金',ROUND(AVG(sal),2) avgsal,
scott-# ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
scott-# COUNT(empno) count
scott-# from emp
scott-# where comm IS NULL;
  ?column?  | avgsal  | avgyear | count
------------+---------+---------+-------
 不领取佣金 | 1400.00 |   35.05 |     4
 领取佣金   | 2342.50 |   33.73 |    10
(2 rows)

多字段分组统计
21查询出每个部门的详细信息
Oracle

SQL> select d.deptno,d.dname,d.loc,
  2  NVL(COUNT(e.empno),0) count,NVL(ROUND(AVG(sal),2),0) avg,
  3  NVL(SUM(sal),0) sum,NVL(MAX(sal),0) max,NVL(MIN(sal),0) min
  4  from emp e,dept d
  5  where e.deptno(+)=d.deptno
  6  GROUP BY d.deptno,d.dname,d.loc;

    DEPTNO DNAME       LOC         COUNT    AVG    SUM        MAX    MIN
---------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
    20 RESEARCH    DALLAS          5       2175  10875       3000    800
    40 OPERATIONS      BOSTON          0      0      0      0      0
    10 ACCOUNTING      NEW YORK        3    2916.67   8750       5000   1300
    30 SALES       CHICAGO         6    1566.67   9400       2850    950

PPAS

scott=# select d.deptno,d.dname,d.loc,
scott-# NVL(COUNT(e.empno),0) count,NVL(ROUND(AVG(sal),2),0) avg,
scott-# NVL(SUM(sal),0) sum,NVL(MAX(sal),0) max,NVL(MIN(sal),0) min
scott-# from emp e,dept d
scott-# where e.deptno(+)=d.deptno
scott-# GROUP BY d.deptno,d.dname,d.loc;
 deptno |   dname    |   loc    | count |   avg   |   sum    |   max   |   min
--------+------------+----------+-------+---------+----------+---------+---------
     10 | ACCOUNTING | NEW YORK |     3 | 2916.67 |  8750.00 | 5000.00 | 1300.00
     30 | SALES      | CHICAGO  |     6 | 1566.67 |  9400.00 | 2850.00 |  950.00
     40 | OPERATIONS | BOSTON   |     0 |       0 |        0 |       0 |       0
     20 | RESEARCH   | DALLAS   |     5 | 2175.00 | 10875.00 | 3000.00 |  800.00
(4 rows)

HAVING子句
22查询出所有平均工资大于2000元的职位信息，平均工资，雇员人数
Oracle

SQL> select job,ROUND(AVG(sal),2),COUNT(empno)
  2  from emp
  3  GROUP BY job
  4  HAVING AVG(sal)>2000;

JOB    ROUND(AVG(SAL),2) COUNT(EMPNO)
---------- ----------------- ------------
PRESIDENT       5000        1
MANAGER          2758.33        3
ANALYST         3000        2

PPAS

scott=# select job,ROUND(AVG(sal),2),COUNT(empno)
scott-# from emp
scott-# GROUP BY job
scott-# HAVING AVG(sal)>2000;
    job    |  round  | count
-----------+---------+-------
 MANAGER   | 2758.33 |     3
 PRESIDENT | 5000.00 |     1
 ANALYST   | 3000.00 |     2
(3 rows)

23列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资，最高工资
Oracle

SQL> select d.deptno,d.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal)
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno
  4  GROUP BY d.deptno,d.dname,d.loc
  5  HAVING COUNT(e.empno)>1;

    DEPTNO DNAME       ROUND(AVG(E.SAL),2) MIN(E.SAL) MAX(E.SAL)
---------- --------------- ------------------- ---------- ----------
    20 RESEARCH           2175        800   3000
    10 ACCOUNTING              2916.67       1300   5000
    30 SALES               1566.67        950   2850

PPAS

scott=# select d.deptno,d.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal)
scott-# from emp e,dept d
scott-# where e.deptno(+)=d.deptno
scott-# GROUP BY d.deptno,d.dname,d.loc
scott-# HAVING COUNT(e.empno)>1;
 deptno |   dname    |  round  |   min   |   max
--------+------------+---------+---------+---------
     10 | ACCOUNTING | 2916.67 | 1300.00 | 5000.00
     30 | SALES      | 1566.67 |  950.00 | 2850.00
     20 | RESEARCH   | 2175.00 |  800.00 | 3000.00
(3 rows)

24显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000元，输出结果按月工资的合计升序排列
Oracle

SQL> select job,SUM(sal) sum
  2  from emp
  3  where job <> 'SALESMAN'
  4  GROUP BY job
  5  HAVING SUM(sal)>5000
  6  ORDER BY sum ASC;

JOB       SUM
---------- ----------
ANALYST      6000
MANAGER      8275

PPAS

scott=# select job,SUM(sal) sum
scott-# from emp
scott-# where job <> 'SALESMAN'
scott-# GROUP BY job
scott-# HAVING SUM(sal)>5000
scott-# ORDER BY sum ASC;
   job   |   sum
---------+---------
 ANALYST | 6000.00
 MANAGER | 8275.00
(2 rows)

本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！