Group Functions

Oracle provides a rich set of features to handle group operations. These features include aggregate functions, the GROUP BY clause, the HAVING clause.

Aggregate functions
An aggregate function summarizes the results of an expression over a number of rows, returning a single value. The general syntax for most of the aggregate functions is as follows:

aggregate_function ([DISTINCT | ALL] expression) The syntax elements are: aggregate_function Gives the name of the function, e.g., SUM, COUNT, AVG, MAX, MIN, etc.

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.

Expressions

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

The following SQL uses the MAX function to find the maximum salary of all employees:

SELECT MAX(SALARY)

FROM EMPLOYEE;

Use of DISTINCT and ALL

Most aggregate functions allow the use of DISTINCT or ALL along with the expression argument. DISTINCT allows us to disregard duplicate expression values, while ALL causes duplicate expression values to be included in the result.

SELECT COUNT(CUST_NBR), COUNT(DISTINCT CUST_NBR), COUNT(ALL CUST_NBR)

 FROM CUST_ORDER;

The GROUP BY Clause

The GROUP BY clause, along with the aggregate functions, groups a result set into multiple groups, and then produces a single row of summary information for each group. For example, if we want to find the total number of orders for each customer, execute the following query:

SELECT CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

GROUP BY CUST_NBR;

GROUP BY Clause with WHERE Clause

While producing summary results using the GROUP BY clause, we can filter records from the table based on a WHERE clause, as in the following example, which produces a count of orders in which the sale price exceeds $25.00 for each customer:

SELECT CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

WHERE SALE_PRICE > 25

GROUP BY CUST_NBR;

The SQL syntax requires that the WHERE clause must come before the GROUP BY clause.

The HAVING Clause

The HAVING clause is closely associated with the GROUP BY clause. The HAVING clause is used to put a filter on the groups created by the GROUP BY clause. If a query has a HAVING clause along with a GROUP BY clause, the result set will include only the groups that satisfy the condition specified in the HAVING clause. The following query returns the number of orders per customer:

SELECT CUST_NBR, COUNT(ORDER_NBR)

FROM CUST_ORDER

 GROUP BY CUST_NBR

 HAVING CUST_NBR < 260;