Aggregate Functions

Aggregate functions perform a specific operation over all rows in a group. Aggregate functions return a single result row based on groups of rows, rather than on single rows. They are commonly used with the GROUP BY clause in a SELECT statement, where the returned rows are divided into groups. If you omit the GROUP BY clause, the aggregate functions in the select list are applied to all the rows in the queried table or view.

Aggregate functions can be specified in the select list or the HAVING clause. See "SELECT" for more information. The value of the expression is computed using each row that satisfies the WHERE clause.

Many aggregate functions that take a single argument can use the ALL or DISTINCT keywords. The default is ALL. See each aggregate function syntax to see if ALL or DISTINCT can be used.

  • Specify DISTINCT in an aggregate function to consider only distinct values of the argument expression.

  • Specify ALL in an aggregate function to consider all values, including duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average for these results is 1.5.

The ROLLUP and CUBE clauses within a GROUP BY clause produce superaggregate rows where the column values are represented by null values. Because the superaggregate rows are denoted by NULL, it can be a challenge to differentiate between query results that include a null value and the superaggregate result. In addition, within the returned subtotals, how do you find the exact level of aggregation for a given subtotal? Use the GROUP_ID, GROUPING and GROUPING_ID functions to resolve these issues.

See "Data Types" for information about the following.

  • Truncation and type conversion that may occur during the evaluation of aggregate functions.

  • Precision and scale of aggregate functions involving numeric arguments.

  • Control of the result type of an aggregate function.

The following is a list of aggregate functions: