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

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)

+ 订阅