7分组统计查询
<h3>学习内容</h3>
<h4>统计函数</h4>
<p>*<em>在开发过程中需要特别注意count(</em>)无值是返回0的**</p>
<pre><code>/*
count(*)：该列的总记录数
sum(列)：该列（数字）总和
avg(列)：平均值
max(列)：最大值
min(列)：最小值
以上五个函数常用，以下三个函数不常用
median(列)：返回中间值
variance(列)：返回方差
stddev(列)：返回标准差
having子句
group by 分组子句
*/
--查出月工资总和
SELECT SUM(sal) 月工资总和 FROM emp ;
SELECT MAX(sal) 最高工资,MIN(sal) 最低工资,AVG(sal) 平均工资,ROUND(AVG(sal),2) 平均工资两位小数 FROM emp ;
SELECT MIN(hiredate) 最早雇佣 ,MAX(hiredate) 最晚雇佣 FROM emp ;
SELECT MEDIAN(sal) 中间工资 FROM emp ;
SELECT VARIANCE(sal) 工资方差,STDDEV(sal) 工资标准差 FROM emp ;
SELECT COUNT(*),COUNT(deptno),COUNT(empno) FROM emp;--由此例可以看出null值不统计
SELECT COUNT(*),COUNT(ename),COUNT(comm),COUNT(job),COUNT(DISTINCT job) FROM emp ;--null值不统计，distinct去除重复值
select COUNT(*),MAX(sal),MIN(comm),AVG(sal),SUM(sal) FROM bonus ;--count()函数即使无值也会返加0</code></pre>
<h4>单字段分组统计</h4>
<pre><code>-- 分组的前提是：同一列存在重复的信息。不重复也可以分组，但是没意义。
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno ;
SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job;
SELECT deptno,COUNT(*) FROM emp ;--会报错。因为没有group by 的时候使用聚合函数，不能与其他字段一起使用
SELECT deptno,ename,COUNT(*) FROM emp GROUP BY deptno;--会报错。有group by的时候可以出现聚合函数和group by 后面指定的字段
--聚合函数可以嵌套使用，嵌套使用后select后再也不能出现任何的字段。取出平均工资最高的部门。
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ;--再有其他字段就会报错
-- 什么时候使用分组
/*
当需要使用聚合函数的同时，又要显示其他列。
当一列中存在很多重复值的时候
*/
-- 查询部门名称、部门人数、部门平均工资\平均服务年限。注意：需要查询的列要作为分组的条码。
SELECT d.dname 部门名称,COUNT(e.empno) 部门人数,ROUND(AVG(e.sal),2) 部门平均工资,ROUND(AVG(months_between(SYSDATE,e.hiredate)/12),2) 平均服务年限 FROM emp e FULL JOIN dept d ON e.deptno=d.deptno GROUP BY d.dname ;
SELECT d.dname,COUNT(e.empno),ROUND(AVG(e.sal),2) avgsal,ROUND(AVG(months_between(SYSDATE,e.hiredate)/12),2) avgyear FROM dept d,emp e WHERE e.deptno(+)=d.deptno GROUP BY d.dname
-- 查询公司各个工资等级雇员的数量、平均工资。对联合表的有重复值的列进行分组
SELECT s.grade, COUNT(*),AVG(sal) FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade
-- 统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数。直接分组不能实现，所以使用union
SELECT comm,round(AVG(sal),2),COUNT(*), ROUND(AVG(months_between(SYSDATE,hiredate)/12),2) FROM emp GROUP BY comm ;--直接对comm分组，comm值各不相同的为一组
SELECT '不领取奖金',round(AVG(sal),2) 平均工资,COUNT(*) 雇员人数, ROUND(AVG(months_between(SYSDATE,hiredate)/12),2) 平均年限 FROM emp WHERE comm IS NULL
UNION
SELECT '领取奖金',round(AVG(sal),2) 平均工资,COUNT(*) 雇员人数, ROUND(AVG(months_between(SYSDATE,hiredate)/12),2) 平均年限 FROM emp WHERE comm IS NOT NULL ;</code></pre>
<h4>多字段分组统计：可以查询到更多的字段，在分组语句中，需要出现在查询字段里的列一定要出现在分组条码里</h4>
<pre><code>-- 查询每个部门详细信息。
SELECT d.deptno 部门编号,d.dname 部门名称,d.loc 部门地址,COUNT(e.empno) 部门人数,NVL(ROUND(AVG(sal),2),0) 平均工资,nvl(SUM(sal),0) 总工资,nvl(MAX(sal),0) 最高工资,nvl(MIN(sal),0) 最低工资 FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno,d.dname,d.loc ;</code></pre>
<h4>HAVING 子句：对分给语句数据进行过滤，类似where。</h4>
<pre><code>--查询出所有平均工资大于2000元的职位信息、平均工资、雇员工数。
SELECT job,ROUND(AVG(sal),2) 平均工资,COUNT(empno) 人数 FROM emp GROUP BY job HAVING AVG(sal) >2000 ;
SELECT d.deptno 部门编号,d.dname 部门名称,COUNT(e.ename) 部门人数, ROUND(AVG(e.sal),2) 平均工资,ROUND(MIN(e.sal),2) 最低工资,ROUND(MAX(e.sal),2) 最高工次 FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno,d.dname HAVING COUNT(e.empno)>1;
--什么时候用where，什么时候用having：有group by才能用having，where是分组前使用，having是分组后使用。
--工作不是SALESMAN，并且同类工作工资总和大于五千的岗位，升序
SELECT job,SUM(sal) FROM emp WHERE job <> 'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal) ASC -- SALESMAN</code></pre>