分组统计查询

分组函数展示

count（*|[distinct]列）：求出全部记录数
如果列上有null值则不统计，distinct重复记录也不统计。建议用count（字段），必须用一个不可能为空字段。
sum：求出总和，操作列为数字
avg：平均值
max：最大值
min：最小值
median：返回中间值
variance：返回方差
stddev：返回标准差

注：所有的统计函数中只有count函数可以在表中没有任何记录的时候返回结果。

单字段分组统计操作

引入：正常情况下都允许重复数据分组，实际上也允许一条数据一组。
group by 子句
select子句中允许分组字段，统计函数

注：分组操作的若干限制
注意事项一：
如果在一个查询中不存在group by 子句，那么在select子句之中只允许出现统计函数，其他的任何字段都不允许出现。
注意事项二：
在统计查询中，select 子句之中只允许出现分组字段和统计函数。
注意事项三：
统计函数允许嵌套使用，但是一旦使用了嵌套的统计函数之后，select子句之中不允许出现任何字段，包括分组字段。
例如：求出每个部门平均工资最高的工资
select max(avg(sal)) from emp group by deptno;
步骤：
多表查询数据统计，临时表分组，行列的组成结果一定是临时表。
先确定表
确定关联字段
根据规则进行分组统计

例如：
统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数。

SELECT '领取佣金', ROUND(AVG(sal),2) avgsal,
    ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
    COUNT(empno) count
FROM emp
WHERE comm IS NOT NULL ;

SELECT '领取佣金', ROUND(AVG(sal),2) avgsal,
    ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
    COUNT(empno) count
FROM emp
WHERE comm IS NULL ;

SELECT '不领取佣金', ROUND(AVG(sal),2) avgsal,
    ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
    COUNT(empno) count
FROM emp
WHERE comm IS NOT NULL 
    UNION
SELECT '领取佣金', ROUND(AVG(sal),2) avgsal,
    ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,
    COUNT(empno) count
FROM emp
WHERE comm IS NULL ;

多字段分组

注：多字段分组是group by 定义的多个字段，且多个字段必须同时重复。
having子句
与group子句进行配合使用，对分组后的数据进行统计过滤，因为where在group by 之前执行，所以where中不允许使用统计函数，所以只能利用having子句。
步骤

1.关联表查出基本数据
2.临时表分组

执行顺序

from->where->group by->having->select->order by

where与having区分

where：在分组之前使用（可以没有group by），不允许使用统计函数
having：是在分组之后时候（必须有group by），允许使用统计函数

例子：
显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列
步骤一

SELECT * 
FROM emp
WHERE job<>'SALESMAN' ;

步骤二

SELECT job , SUM(sal) sum
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job ;

步骤三、

SELECT job , SUM(sal) sum
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000 ;

步骤四、

SELECT job , SUM(sal) sum
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY sum ASC ;

特别注意：由于order by 最后执行，所以可以使用select里的别名。

总结

戒骄戒躁