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:
.gif "QuerySummarizingDataShowResultsPane screenshot")
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