Aggregate Functions

To summarize all the data in a table, you create an aggregate query that involves a function such as SUM( ) or AVG( ). When you run the query, the result set contains a single row with the summary information.

For example, you can calculate the total price of all books in the Titles table by creating a query that sums the contents of the Price column. The resulting query output might look like this:

The corresponding SQL statement might look like this:

SELECT SUM(price) total_price
FROM titles

You can use the following aggregate functions:

Aggregate function	Description
AVG(expr)	Average of the values in a column. The column can contain only numeric data.
COUNT(expr), COUNT(*)	A count of the values in a column (if you specify a column name as expr) or of all rows in a table or group (if you specify *). COUNT(expr) ignores null values, but COUNT(*) includes them in the count.
MAX(expr)	Highest value in a column (last value alphabetically for text data types). Ignores null values.
MIN(expr)	Lowest value in a column (first value alphabetically for text data types). Ignores null values.
SUM(expr)	Total of values in a column. The column can contain only numeric data.

Note

Oracle supports additional aggregate functions.

When you use an aggregate function, by default the summary information includes all specified rows. In some instances, a result set includes non-unique rows. You can filter out non-unique rows by using the DISTINCT option of an aggregate function.

You can combine aggregate functions with other expressions to calculate other summary values. For details, see How to: Summarize or Aggregate Values Using Custom Expressions.

See Also

Concepts

Working with Data in the Results Pane

Other Resources

Working with Stored Procedures and User-Defined Functions