ORACLE提供了丰富的功能支持分组操作，包括聚集函数，GROUP BY子句，HAVING子句，以及GROUP BY子句功能的扩展--ROLLUP,CUBE和GROUPING SETS。

1.聚集函数语法：

aggregate_function([DISTINCT | ALL] expression)

The syntax elements are:

aggregate_function

Gives the name of the function—e.g., SUM, COUNT, AVG, MAX, MIN

 

DISTINCT

Specifies that the aggregate function should consider only distinct values of the argument expression.

 

ALL

Specifies that the aggregate function should consider all values, including all duplicate values, of the argument expression. The default is ALL.

 

expression

Specifies a column, or any other expression, on which you want to perform the aggregation.

注意当我们使用如下查询语句：

SELECT COUNT(*), COUNT(sale_price) FROM cust_order;

当sale_price列允许NULL时，我们可能会得到如下结果：

COUNT(*) COUNT(SALE_PRICE)

--------      -----------------

20             14

这是因为COUNT(sale_price)忽略NULL值造成的！

除了COUNT(*)之外，只有一个聚集函数（GROUPING）不忽略NULL值！

聚集函数中不允许使用DISTINCT的包括：

CORR, COVAR_POP, COVAR_SAMP, and all the linear regression functions

STTDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, and GROUPING

2.GROUP BY子句

当使用GROUP BY子句写一个查询语句时，有许多规则你需要知道。一般来说，任何在你select子句中的非聚集表达式必须出现在你的GROUP BY子句中。

• SELECT语句中同时使用聚集表达式和非聚集表达式一般需要一个GROUP BY子句

SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
FROM cust_order;
SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
*
ERROR at line 1:
ORA-00937: not a single-group group function

• GROUP BY子句必须包括所有的非聚集表达式

SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr;
SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

• 聚集表达式不能包含在GROUP BY子句中

SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr, COUNT(order_nbr);
GROUP BY cust_nbr, COUNT(order_nbr)
*
ERROR at line 3:
ORA-00934: group function is not allowed here

• 常量可以从GROUP BY子句中被忽略

SELECT 'CUSTOMER', cust_nbr, COUNT(order_nbr) FROM cust_order GROUP BY cust_nbr;

• 当标量函数被使用在SELECT列表中的某列，并不强迫你要在GROUP BY子句中包括这个标量函数

SELECT SUBSTR(lname,1,1), COUNT(*) FROM employee GROUP BY lname;

• 连接的列可以使用两种方法分组

SELECT manager_emp_id || job_id, COUNT(*) FROM employee GROUP BY manager_emp_id || job_id;

SELECT manager_emp_id || job_id, COUNT(*) FROM employee GROUP BY manager_emp_id, job_id;