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 expressionPPAS
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 3PPAS
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.66667PPAS
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 functionPPAS
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 8750PPAS
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.99PPAS
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 1550PPAS
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 10PPAS
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 950PPAS
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 2PPAS
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 2850PPAS
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 8275PPAS
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)数据库的差异，以帮助更多读者了解如何实现数据库迁移！