Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in
HAVING clauses. They are commonly used with the
BY clause in a
SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a
BY clause, the elements of the select list can be aggregate functions,
BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the
BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the
HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules for expressions in the
BY clause of an aggregate function
Many (but not all) aggregate functions that take a single argument accept these clauses:
UNIQUE, which are synonymous, cause an aggregate function to consider only distinct values of the argument expression. The syntax diagrams for aggregate functions in this chapter use the keyword
DISTINCT for simplicity.
ALL causes an aggregate function to consider all values, including all duplicates.
For example, the
DISTINCT average of 1, 1, 1, and 3 is 2. The
ALL average is 1.5. If you specify neither, then the default is
Some aggregate functions allow the
windowing_clause, which is part of the syntax of analytic functions. Refer to windowing_clause for information about this clause. In the listing of aggregate functions at the end of this section, the functions that allow the
windowing_clause are followed by an asterisk (*)
All aggregate functions except
GROUPING_ID ignore nulls. You can use the
NVL function in the argument to an aggregate function to substitute a value for a null.
REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
The aggregate functions
STDDEV, when followed by the
KEEP keyword, can be used in conjunction with the
LAST function to operate on a set of values from a set of rows that rank as the
LAST with respect to a given sorting specification. Refer to FIRST for more information.
You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id; AVG(MAX(SALARY)) ---------------- 10926.3333
This calculation evaluates the inner aggregate (
salary)) for each group defined by the
BY clause (
department_id), and aggregates the results again.
In the list of aggregate functions that follows, functions followed by an asterisk (*) allow the