综合数据和分组函数
分组函数是对一批(一组)数据进行操作(综合)之后返回一个值。这批数据可能是整个表，也可能是按某种条件把该表分成的组。
一、分组函数
Oracle常用的分组函数有以下5个：
COUNT、AVG、SUM、MAX、MIN
1.COUNT的格式如下：
COUNT {*[DISTINCT|ALL] 表达式}
该函数返回查询的行数。
SQL> select count(*) 2 from emp; COUNT(*) ---------- 14
备注：COUNT(*)返回表中所有的行包括空行和重复的行。
2.AVG和SUM函数
AVG函数的格式如下：
AVG ([DISTINCT|ALL]表达式)
该函数返回表达式的平均值。
SUM函数的格式如下：
SUM ([DISTINCT|ALL]表达式)
该函数返回表达式的总和。
SQL> select avg(sal) "Average Salary",sum(sal) "Summary",count(sal) "Records" 2 from emp; Average Salary Summary Records -------------- ---------- ---------- 2073.21429 29025 14
3.MIN和MAX函数
MAX函数的格式如下：
MAX ([DISTINCT|ALL]表达式)
该函数返回表达式的最大值。
MIN函数的格式如下：
MIN([DISTINCT|ALL]表达式)
该函数返回表达式的最小值。
SQL> select min(sal) "Lowest Salary",max(sal) "Highest Salary" 2 from emp; Lowest Salary Highest Salary ------------- -------------- 800 5000
备注：不像AVG和SUM函数只能操作数字型数据，MIN和MAX函数不但可用于数字型数据，而且还可以用于字符型数据和日期型数据。
SQL> select min(job),max(job) 2 from emp; MIN(JOB) MAX(JOB) --------- --------- ANALYST SALESMAN SQL> select min(hiredate) "First Day",max(hiredate) "Last Day" 2 from emp; First Day Last Day --------- --------- 17-DEC-80 23-MAY-87
4.GROUP BY 子句
a.改变GROUP BY子句的排序次序
SQL> select job,avg(sal) "Average Salary" 2 from emp 3 group by job 4 order by "Average Salary" desc; JOB Average Salary --------- -------------- PRESIDENT 5000 ANALYST 3000 MANAGER 2758.33333 SALESMAN 1400 CLERK 1037.5
b.GROUP BY子句的特殊用法
GROUP BY 子句中的列可以不在SELECT列表中。
SQL> select avg(sal) "Average Salary" 2 from emp 3 group by job; Average Salary -------------- 1037.5 1400 5000 2758.33333 3000
备注：为了提高结果的可读性，应尽可能不使用这样的查询方法。
c.分组函数与GROUP BY子句的非法操作
SQL> select job,avg(sal) 2 from emp; select job,avg(sal) * ERROR at line 1: ORA-00937: not a single-group group function
备注：如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在GROUP BY子句中。
5.HAVING子句的使用
如果老板只想知道平均工资高于1500元的职位(工种)，该如何改写下面的查询语句？
SQL> select job,avg(sal) "Average Salary" 2 from emp 3 group by job 4 order by "Average Salary" desc; JOB Average Salary --------- -------------- PRESIDENT 5000 ANALYST 3000 MANAGER 2758.33333 SALESMAN 1400 CLERK 1037.5
例如改成这样：
SQL> select job,avg(sal) 2 from emp 3 where avg(sal) > 1500 4 group by job; where avg(sal) > 1500 * ERROR at line 3: ORA-00934: group function is not allowed here
这是因为WHERE子句不能用于限制分组函数。
在Oracle中您可以使用HAVING子句来限制分组函数。
SQL> select job,avg(sal) 2 from emp 3 group by job 4 having avg(sal) > 1500 5 order by 2; JOB AVG(SAL) --------- ---------- MANAGER 2758.33333 ANALYST 3000 PRESIDENT 5000
当使用了HAVING子句时，Oracle系统处理的顺序如下：
(1)、首先对数据行(记录)进行分组；
(2)、把所得到的的分组应用于分组函数；
(3)、最后显示满足HAVING子句所指定条件的结果。
6.分组函数的嵌套
查询以工作分类的平均工资中最低和最高，但是不包括总裁。
SQL> select min(avg(sal)),max(avg(sal)) 2 from emp 3 where job not like 'PRESI%' 4 group by job; MIN(AVG(SAL)) MAX(AVG(SAL)) ------------- ------------- 1037.5 3000
查询语句中使用了两层的分组函数的嵌套。在Oracle系统中嵌套的分组函数计算顺序是由内到外的。也就是说，在Oracle系统中按如下的顺序来执行：
(1)、在emp表中找到所有职位(job)不是以PRESI开头的数据行(记录)；
(2)、将这些数据行(记录)按职位(job)分类；
(3)、求出每一类的平均工资；
(4)、最后求出这些平均工资的最小值和最大值。
与单值函数不同，分组函数只能嵌套两层。
备注：尽管分组函数给我们编写SQL语句带来了很大的方便，但是使用起来可能会使系统的效率明显下降，特别是在对容量大的表格进行这样的操作时。因为使用分组函数通常要扫描整个表，如果使用了GROUP BY子句，Oracle还要进行排序。
7.分组函数的空值问题
除了COUNT(*)以外，其他的分组函数都不处理空值(NULL)。
8.NVL函数在分组函数中的使用
如果想让所有的员工都参加平均，可借助于NVL函数。
SQL> select avg(nvl(comm,0)) "Average Commission" 2 from emp; Average Commission ------------------ 157.142857
