Aggregate functions summarize data over a group of rows from the database.
Aggregate function:
AVG ( aggregate-parm )
| COUNT ( * )
| COUNT ( aggregate-parm )
| LIST ( aggregate-parm )
| MAX ( aggregate-parm )
| MIN ( aggregate-parm )
| SUM ( aggregate-parm )
aggregate-parm:
DISTINCT column-name
| expression
Aggregate functions summarize data over a group of rows from the database. The groups are formed using the GROUP BY clause of the SELECT statement. Aggregate functions are only allowed in the select list and in the HAVING and ORDER BY clauses of a SELECT statement.
AVG( numeric-expr ) Computes the average of numeric-expr for each group of rows. This average does not include rows where the expression is the NULL value. Returns the NULL value for a group containing no rows.
AVG( DISTINCT column-name ) Computes the average of the unique values in column-name. This is of limited usefulness, but is included for completeness.
COUNT( * ) Returns the number of rows in each group.
COUNT( expression ) Returns the number of rows in each group where the expression is not the NULL value.
COUNT( DISTINCT column-name ) Returns the number of different values in the column with name column-name. Rows where the value is the NULL value are not included in the count.
LIST( string-expr ) Returns a string containing a comma-separated list composed of all the values for string-expr in each group of rows. Rows where string-expr is the NULL value are not added to the list.
LIST( DISTINCT column-name) Returns a string containing a comma-separated list composed of all the different values for string-expr in each group of rows. Rows where string-expr is the NULL value are not added to the list.
MAX( expression ) Returns the maximum expression value found in each group of rows. Rows where expression is the NULL value are ignored. Returns the NULL value for a group containing no rows.
MAX( DISTINCT column-name ) Returns the same as MAX(expression), and is included for completeness.
MIN( expression ) Returns the minimum expression value found in each group of rows. Rows where expression is the NULL value are ignored. Returns the NULL value for a group containing no rows.
MIN( DISTINCT column-name ) Returns the same as MIN( expression ), and is included for completeness.
SUM( expression ) Returns the total of expression for each group of rows. Rows where the expression is the NULL value are not included. Returns NULL for a group containing no rows.
SUM( DISTINCT column-name ) Computes the sum of the unique values for numeric-expr for each group of rows. This is of limited usefulness, but is included for completeness.