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: