分组函数
多行函数也叫做组函数,分组函数作用于一组数据,并对一组数据返回一个值,
如,AVG,COUNT,MAX,MIN,SUM,操作的是一组数据 ,返回一个结果,组函数能自动滤空
SQL> select sum(sal) from emp; SUM(SAL) ---------- 29025 SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> select sum(sal)/count(*) from emp; SUM(SAL)/COUNT(*) ----------------- 2073.21429 SQL> select avg(sal) from emp; AVG(SAL) ---------- 2073.21429 SQL> select sum(comm)/count(*) from emp; SUM(COMM)/COUNT(*) ------------------ 157.142857 SQL> select avg(comm) from emp; AVG(COMM) ---------- 550 SQL>
统计工种
SQL> select count(distinct job) from emp; COUNT(DISTINCTJOB) ------------------ 5 SQL>
组函数都有自动滤空功能(忽略空值)
分组数据
group by
按照group by 后面的表达式将 from 后面的table 进行分组。针对每一组使用组函数
查询部门的平均工资
SQL> select avg(sal) from emp group by deptno; AVG(SAL) ---------- 1566.66667 2175 2916.66667
注意:在SELECT列表中所有没有包含在组函数中的列,都必须在group by 的后面出现
SQL> select ename,deptno,avg(sal) from emp group by deptno; select ename,deptno,avg(sal) from emp group by deptno * 第 1 行出现错误: ORA-00979: 不是 GROUP BY 表达式 已用时间: 00: 00: 00.04 SQL> select ename,deptno,avg(sal) from emp group by deptno,ename; ENAME DEPTNO AVG(SAL) ---------- ---------- ---------- JONES 20 2975 WARD 30 1250 SCOTT 20 3000 KING 10 5000 JAMES 30 950 ALLEN 30 1600 MARTIN 30 1250 BLAKE 30 2850 FORD 20 3000 SMITH 20 800 ADAMS 20 1100 MILLER 10 1300 CLARK 10 2450 TURNER 30 1500 已选择14行。 已用时间: 00: 00: 00.01 SQL> select deptno,avg(sal) from emp group by deptno; DEPTNO AVG(SAL) ---------- ---------- 30 1566.66667 20 2175 10 2916.66667 已用时间: 00: 00: 00.02
Having
使用having过滤分组:
1.行已经被分组
2.使用了组函数
3.满足having子句中条件的分组将被显示
其语法:
SELECT column,group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]
已写入 file afiedt.buf 1 SELECT deptno,avg(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING avg(sal) > 2000 5* ORDER BY deptno SQL> / DEPTNO AVG(SAL) ---------- ---------- 10 2916.66667 20 2175 已用时间: 00: 00: 00.02
不能在where子句中使用组函数
可以在having子句中使用组函数
group by 语句的增强
按部门统计各个部门不同工种的工资情况
SQL> ed 已写入 file afiedt.buf 1 SELECT deptno,job,sum(sal) 2 FROM emp 3 GROUP BY rollup(deptno,job) 4* ORDER BY deptno SQL> / DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 29025 已选择13行。 已用时间: 00: 00: 00.05 SQL>
group by deptno,job + group by deptno + group by null =group by rollup(deptno,job)