《卸甲笔记》-分组统计查询

---------- ----------------- ---------- ----------

2073.21429 2073.21 5000 800

PPAS

scott=# select AVG(sal),ROUND(AVG(sal),2),MAX(sal),MIN(sal) from emp;

avg | round | max | min

-----------------------+---------+---------+--------

2073.2142857142857143 | 2073.21 | 5000.00 | 800.00

(1 row)

3统计出公司最早雇佣和最晚雇佣的雇佣日期

Oracle

SQL> select MIN(hiredate) 最早雇佣日期,MAX(hiredate) 最晚雇佣日期 from emp;

最早雇佣日期最晚雇佣日期

------------ ------------

17-DEC-80 23-MAY-87

PPAS

scott=# select MIN(hiredate) 最早雇佣日期,MAX(hiredate) 最晚雇佣日期 from emp;

最早雇佣日期 | 最晚雇佣日期

--------------------+--------------------

17-DEC-80 00:00:00 | 23-MAY-87 00:00:00

(1 row)

4统计公司中间的工资值

Oracle

SQL> select MEDIAN(sal) from emp;

MEDIAN(SAL)

-----------

1550

PPAS

PPAS中没有求中位数的函数

5统计工资的标准差与方差

Oracle

SQL> select STDDEV(sal),VARIANCE(sal) from emp;

STDDEV(SAL) VARIANCE(SAL)

----------- -------------

1182.50322 1398313.87

PPAS

scott=# select STDDEV(sal),VARIANCE(sal) from emp;

stddev | variance

-------------------+----------------------

1182.503223516272 | 1398313.873626373626

(1 row)

6统计出公司的雇员人数

Oracle

SQL> select COUNT(empno),COUNT(*) from emp;

COUNT(EMPNO) COUNT(*)

------------ ----------

14 14

PPAS

scott=# select COUNT(empno),COUNT(*) from emp;

count | count

-------+-------

14 | 14

(1 row)

7验证COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的使用区别

Oracle

SQL> select COUNT(*),COUNT(ename),COUNT(comm),COUNT(DISTINCT job) from emp;

COUNT(*) COUNT(ENAME) COUNT(COMM) COUNT(DISTINCTJOB)

---------- ------------ ----------- ------------------

14 14 4 5

PPAS

scott=# select COUNT(*),COUNT(ename),COUNT(comm),COUNT(DISTINCT job) from emp;

count | count | count | count

-------+-------+-------+-------

14 | 14 | 4 | 5

(1 row)

8验证3中COUNT()函数的使用方式

Oracle

SQL> select COUNT(ename),AVG(sal),SUM(sal),MAX(sal),MIN(sal) from bonus;

COUNT(ENAME) AVG(SAL) SUM(SAL) MAX(SAL) MIN(SAL)

------------ ---------- ---------- ---------- ----------

PPAS

scott=# select COUNT(ename),AVG(sal),SUM(sal),MAX(sal),MIN(sal) from bonus;

count | avg | sum | max | min

-------+-----+-----+-----+-----

0 | | | |

(1 row)

单字段分钟统计

9统计出每个部门的人数

Oracle

SQL> select deptno,COUNT(*)

2 from emp

3 GROUP BY deptno;

DEPTNO COUNT(*)

---------- ----------

30 6

20 5

10 3

PPAS

scott=# select deptno,COUNT(*)

scott-# from emp

scott-# GROUP BY deptno;

deptno | count

--------+-------

20 | 5

30 | 6

10 | 3

(3 rows)

10统计出每种职位的最低工资和最高工资

Oracle

SQL> select job,MIN(sal),MAX(sal)

2 from emp

3 GROUP BY job;

JOB MIN(SAL) MAX(SAL)

---------- ---------- ----------

CLERK 800 1300

SALESMAN 1250 1600

PRESIDENT 5000 5000

MANAGER 2450 2975

ANALYST 3000 3000

PPAS

scott=# select job,MIN(sal),MAX(sal)

scott-# from emp

scott-# GROUP BY job;

job | min | max

-----------+---------+---------

SALESMAN | 1250.00 | 1600.00

CLERK | 800.00 | 1300.00

MANAGER | 2450.00 | 2975.00

PRESIDENT | 5000.00 | 5000.00

ANALYST | 3000.00 | 3000.00

(5 rows)

11在没有分组语句（GROUP BY）时使用统计函数后出现其他字段

Oracle

SQL> select deptno,COUNT(EMPNO) from emp;

select deptno,COUNT(EMPNO) from emp

ERROR at line 1:

ORA-00937: not a single-group group function

PPAS

scott=# select deptno,COUNT(EMPNO) from emp;

ERROR: column "emp.deptno" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: select deptno,COUNT(EMPNO) from emp;

12在没有分组的时候只允许单独使用统计函数

Oracle

SQL> select COUNT(empno) from emp;

COUNT(EMPNO)

------------

PPAS

scott=# select COUNT(empno) from emp;

count

-------

(1 row)

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.66667s

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)