统计函数对比
1查询出公司每个月支出的月工资总和
Oracle
SQL> select SUM(sal) from emp;
SUM(SAL)
----------
29025
PPAS
scott=# select SUM(sal) from emp;
sum
----------
29025.00
(1 row)
2查询出公司的最高工资，最低工资，平均工资
Oracle
SQL> select AVG(sal),ROUND(AVG(sal),2),MAX(sal),MIN(sal) from emp;
AVG(SAL) ROUND(AVG(SAL),2) MAX(SAL) MIN(SAL)
---------- ----------------- ---------- ----------
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)
------------ ---------- ---------- ---------- ----------
0
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)
------------
14
PPAS
scott=# select COUNT(empno) from emp;
count
-------
14
(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)