6. 分组统计查询

2.9.SQL语句----》分组统计查询

2.9.1分组统计查询

也属于简单查询
1.如果要想进行分组统计，实际上需要考虑2个层次：
1）分组操作------》GROUP BY 子句进行分组
2）统计操作-----》又叫做统计函数/组函数/分组函数
SUM（），AVG（），MIN（），MAX（）,COUNT()

1.统计函数（分组函数，组函数）
之前接触过的count（）函数，这个函数主要作用是可以统计出数据表中的数据行的个数，因此count()函数属于一个统计函数，而与之类似的还有：SUM（），AVG（），MIN（），MAX（），当然Oracle中提供了其他的很多统计函数，但是我们常用的就这5个统计函数。
例如：查看从事salesman工作的员工的个数
SELECT COUNT() FROM emp WHERE job=’SALESMAN’;
查询雇员的最高工资。
SELECT ename, MAX() FROM emp;----->会报错
ORA-00937: not a single-group group function----》提示ename不是单组的分组函数

在未分组的情况下，SELECT子句只能写分组函数，而不能与字段同时出现。

SQL> SELECT MAX(sal),AVG(sal),MIN(hiredate),COUNT(sal)
2 FROM emp;

1)COUNT()---->统计个数（重点）
对于统计函数而言，COUNT()最为常用的函数
Count（）函数的主要功能是统计记录个数，如果表中没有记录，那么count（）统计结果为0；而不是null，null表示什么都没有。

这个null和0只是在数据库层次有所区分，如果在程序的执行之中，null接受的类型是整数，则自动变为0

A 对于count（）函数有三种用法—面试题
关于count函数的面试题
Count（*）-----》*对行进行统计
Count(字段)-----》对字段的值进行统计
Count（distinct 字段）----》先对字段去重，然后在统计该字段的值

a）统计出数据表中的数据行的数量----》 count()
SELECT COUNT()
FROM emp;

b）统计数据表中sal字段的值的数量，null不统计，
如果统计的字段的值都是非空的，使用count（字段）和count（*）的结果是相同的----》
SELECT COUNT(sal)
FROM emp;

c）先将job字段的重复项去除，然后在对去重后的job字段的值进行统计

B.Count函数常用的方法
进行count（）统计的时候都会采用count（）的形式完成处理
2）其他统计函数
SUM()------》求和
AVG()-----》求平均值、
MIN()------》求最小值
MAX()-----》求最大值
3）例如
1.查询公司的人数，平均工资，每月的总支出
SQL> SELECT COUNT(),AVG(sal),sum(sal)
2 FROM emp;

2.查询公司的最低最高工资
SQL> SELECT MIN(sal),MAX(sal)
2 FROM emp;

3查询公司的平均服务年限
SQL> SELECT AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12)
2 FROM emp;

将平均数保留2位小数
SQL> SELECT ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2)
2 FROM emp;

4查询公司的最早一次雇佣日期和最后一次的雇佣日期
SQL> SELECT MAX(hiredate),MIN(hiredate)
2 FROM emp;

7）统计函数后不能正确执行的原因
如果程序中使用统计函数，：
1．使用了GROUP BY 子句，并指定了分组的条件，SELECT 后面可以加分组字段，统计函数，在使用统计函数时，不能出现统计函数和分组条件之外的字段。
2．如果没有使用GROUP BY 子句，还要使用统计函数，那么SELECT后面只能加统计函数，只能单独的使用统计函数，不能加字段。
2.分组统计----GROUP BY子句
想要使用分组统计，则首先要学习其语法格式，使用GROUP BY 进行分组。
执行顺序：先from—后where----后group by----后select----order by
使用 groupby子句可以将数据分成不同的组去统计，
如果不使用groupby分组，那么直接使用统计函数只能分成单个组统计
----》select * from emp where deptno=20----》将数据筛选为只要编号为20的数据，这其实就是一种分组操作，只不过这是单个分组
1）SQL分组操作语法格式如下
SELECT [DISTINCT] * | 列名1【别名1】，列名2 【别名2】。。。。| 计算过程 | 统计函数
FROM 数据表1 【别名1】
【WHERE 限定条件1，限定条件2。。。。；】
【GROUP BY 分组条件1，分组条件2。。。。。。】
分组条件可以是字段，关系
【ORDER BY 排序字段1 【ASC|DESC】, 排序字段2【ASC|DESC】……】；
当使用了group by子句后，可以在SELECT 子句中使用分组字段和统计函数，不能使用其他字段。
2）HAVING子句
也是一种过滤查询，通常和GROUP BY子句联合在一起使用，是分组后的条件过滤
是分组执行后在执行having子句
执行顺序：先from—后where----后group by—后having-----后select----order by
1）基本语法
SELECT [DISTINCT] * | 列名1【别名1】，列名2 【别名2】。。。。| 计算过程 | 统计函数
FROM 数据表1 【别名1】，数据表2 【别名2】
【WHERE 限定条件1，限定条件2。。。。；】
【GROUP BY 分组条件1，分组条件2。。。。。。】
分组条件可以是字段，关系
【HAVING 分组后过滤条件1，限定条件2。。。。；】
【ORDER BY 排序字段1 【ASC|DESC】, 排序字段2【ASC|DESC】……】；
以分号；结束
3）例如
A.查询出每个部门的部门编号及雇员数量
—》确定表： emp
—》确定字段：deptno COUNT(*)
—》确定分组条件： deptno----》按照不同的部门编号，将员工分为三组
SQL> SELECT deptno,count(ename)
2 FROM emp
3 GROUP BY deptno;

SQL> SELECT deptno,COUNT(*)
2 FROM emp
3 GROUP BY deptno;

当使用了group by子句后，可以在SELECT 子句中使用分组字段和统计函数
B．求出每个部门的平均工资
SQL> SELECT deptno,AVG(sal)
2 FROM emp
3 GROUP BY deptno;

B.按部门分组，并显示部门的名称，部门的员工数量
a）SQL> SELECT dname,COUNT(ename)
2 FROM emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno)
3 GROUP BY dname;

b）分析过程1：-----这是错误的需要分析
SQL> SELECT dname,COUNT()
2 FROM emp,dept
3 WHERE emp.deptno=dept.deptno
4 GROUP BY dname;
上述语句显示结果为3行，因为where后的条件导致40部门没有显示，我们需要获取所有部门信息，而我们的dept表为右表，则需要使用右外连接。
分析过程2：-----这是错误的需要分析
SQL> SELECT dname,COUNT()
2 FROM emp,dept
3 WHERE emp.deptno（+）=dept.deptno
4 GROUP BY dname;
在多表查询时，使用了外连接，上述查询结果中包含operation分组，但该分组中并没有真正的员工，执行时，operations部门，缺显示一个员工
Count（*）统计行数-----》因为有operation所以统计为1
Count（e.ename）统计员工名称字段为-----所以统计为0 ）
c）注意：
对于上述要求，我们需要按照部门分组，分组条件d.dname，理论上来讲，我们的分组条件可以是d.deptno或者d.loc
SQL> SELECT dname,COUNT(ename)
2 FROM emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno)
3 GROUP BY dname,loc;

C.要求显示出平均工资大于2000的部门编号和平均工资
----确定表：emp
----确定字段：部门编号，平均工资avg（sal）
----having限定：平均工资大于2000
----分组：部门编号
SELECT deptno,AVG(sal)
FROM emp
WHERE AVG(sal) > 2000
GROUP BY deptno;
ORA-00934: group function is not allowed here
—》在使用了GROUP BY 分组时 where后面不允许使用统计函数
需要配合HAVING 限定查询使用
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000;

D.要求显示20,30,10部门中平均工资大于2000的部门编号和平均工资
----确定表：emp
----确定字段：部门编号，平均工资avg（sal）
----where限定：20，30.10部门
----where限定：平均工资大于2000
----分组：部门编号

E显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列。
SQL> SELECT job,SUM(SAL)
2 FROM emp
3 WHERE job != ‘SALESMAN’
4 GROUP BY job
5 HAVING SUM(sal)>5000
6 ORDER BY SUM(SAL) ASC;

F按照职位分组，查询出平均工资高于1200的所有职位信息以及该职位的对应人数，平均工资
------确定表： emp
------确定字段：JOB,count (Ename)， avg（sal）
------确定分组条件group by job
-------确定分组后的限定条件：avg(sal)>1200
SQL> SELECT job,COUNT(ename),AVG(sal)
2 FROM emp
3 GROUP BY job
4 HAVING AVG(sal)>1200
5 ORDER BY AVG(sal);

G.查询出20，30部门中平局工资高于1200所有职位信息以及该职位对应人数，平均工资
SELECT job,deptno,COUNT(ename),AVG(sal)
FROM emp
WHERE deptno IN (20,30)
GROUP BY job,deptno
HAVING AVG(sal)>1200
ORDER BY AVG(sal);

2.9.2SQL语句-----》分组多表查询

也属于简单查询
之前进行的分组统计都是采用了单表的形式完成处理，多表查询也可以实现分组统计操作
1.例如
1)查询出每个部门的名称，人数，平均工资
------确定表：dept ，emp
------确定字段：部门名称，enamel，avg(sal)
------where e.deptno=d.deptno
------group by dname
SQL> SELECT d.dname,COUNT(e.ename), AVG (e.sal)
2 FROM emp e, dept d
3 WHERE e.deptno(+)=d.deptno
4 GROUP BY d.dname;

2)查询出每个工资等级对应的人数以及此等级的最高工资
------确定表：salgrade ，emp
------确定字段：grade，max（sal），count (ename)
------确定限定条件where e.sal BETWEEN s.losal and s.hisal
------确定分组条件group by grade
SELECT s.grade,MAX(e.sal),COUNT(e.ename)
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade
ORDER BY s.grade ASC;

排序后

3)查询出每个部门的编号，名称，位置，部门人数，平均工资
------确定表：dept ，emp
------确定字段：dname，detpno，count (dname)，loc，avg（sal）
------确定限定条件where e.deptno=d.deptno
------确定分组条件group by dname，deptno
SQL> SELECT d.deptno,d.dname,d.loc,COUNT(e.ename),AVG(e.sal)
2 FROM emp e,dept d
3 WHERE e.deptno(+)=d.deptno
4 GROUP BY d.dname,d.loc,d.deptno
5 ORDER BY d.deptno ASC;

只有在GROUP BY 中出现的字段才能在SELECT 子句中出现
4)查询所有非销售人员的工资总和，并且要求满足同一工作的雇员的工资总和大于5000，显示的结果按照工资的总和由高到低排序
SQL> SELECT job,SUM(sal)
2 FROM emp
3 WHERE job != ‘SALESMAN’
4 GROUP BY job
5 HAVING SUM(sal)>5000
6 ORDER BY SUM(sal) DESC;

5)统计出所有领取佣金和不领取佣金的雇员的人数及平均工资
----确定表：emp
-----确定字段：count（ename），avg（sal）comm
需要自己指定分组字段，可以自己起名
SQL> SELECT ’ NO COMM’ comm ,COUNT(),AVG(SAL)
2 FROM EMP
3 WHERE ‘NO COMM’ IS NULL
4 UNION ALL
5 SELECT ‘COMM’ comm ,COUNT(),AVG(SAL)
6 FROM EMP
7 WHERE ‘COMM’ IS NOT NULL;

2.9.3总结

1.分组统计查询
分组操作：group by
统计操作：统计函数/分组函数/组函数 count（）max（）min（），avg（），sum（）
2.HAVING和WHERE的区别
WHERE是在GROUP BY分组前使用，表示要对分组的数据进行筛选，不允许使用统计函数
HAVING是在GROUP BY 分组后使用，允许使用统计函数，针对分组后的数据进行筛选
3.终极格式
SELECT [DISTINCT] * | 列名1【别名1】，列名2 【别名2】。。。。| 计算过程 | 统计函数 | 分组字段
FROM 数据表1 【别名1】，数据表2 【别名2】
【WHERE 限定条件1，限定条件2。。。。；】
【GROUP BY 分组条件1，分组条件2。。。。。。】
分组条件可以是字段，关系
【HAVING 分组后过滤条件1，分组后过滤条件1。。。】
【ORDER BY 排序字段1 【ASC|DESC】, 排序字段2【ASC|DESC】……】；
以分号；结束

SELECT 确定数据列
WHERE 确定数据行
FROM 确定数据表
GROUP BY 针对确定的行分组
HAVING对分组进行过滤
ORDER BY 排序处理