Mastering Oracle SQL, 2nd Edition

Chapter 4. Group Operations

Group operations are quite common in the day-to-day life of a SQL programmer. When you use SQL to access a database, it is quite common to expect questions such as:

• What is the maximum salary in this department?

• How many managers are there in each department?

• What is the number of customers for each product?

• Can you print the monthly aggregate sales for each region?

You need group operations to answer these questions. Oracle provides a rich set of features to handle group operations. These features include aggregate functions, the GROUP BY clause, the HAVING clause, and the extensions to the GROUP BY clause—ROLLUP, CUBE, and GROUPING SETS.

Tip

This chapter deals with simple group operations involving the aggregate functions, the GROUP BY and HAVING clauses. Advanced group operations such as ROLLUP, CUBE, and GROUPING SETS are discussed in Chapter 13.

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

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.