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.
Simple aggregate functions, such as SUM(), MIN(), MAX(), AVG() and COUNT() are allowed only in the select list and in the HAVING and ORDER BY clauses of a SELECT statement. These functions summarize data over a group of rows from the database. Groups are formed using the GROUP BY clause of the SELECT statement.
A new class of aggregate functions, called window functions, provides moving averages and cumulative measures that compute answers to queries such as, “What is the quarterly moving average of the Dow Jones Industrial average,” or “List all employees and their cumulative salaries for each department.”
Simple aggregate functions, such as AVG(), COUNT(), MAX(), MIN(), and SUM() summarize data over a group of rows from the database. The groups are formed using the GROUP BY clause of the SELECT statement.
Newer statistical aggregate functions that take one argument include STDDEV(), STDDEV_SAMP(), STDDEV_POP(), VARIANCE(), VAR_SAMP(), and VAR_POP().
Both the simple and newer categories of aggregates can be used as a windowing function that incorporates a <window clause> in a SQL query specification (a window) that conceptually creates a moving window over a result set as it is processed.
Another class of window aggregate functions supports analysis of time series data. Like the simple aggregate and statistical aggregate functions, you can use these window aggregates with a SQL query specification (or window-spec). The time series window aggregate functions calculate correlation, linear regression, ranking, and weighted average results:
ISO/ANSI SQL:2008 OLAP functions for time series analysis include: CORR(), COVAR_POP(), COVAR_SAMP(), CUME_DIST(), FIRST_VALUE(), LAST_VALUE(), REGR_AVGX(), REGR_AVGY(), REGR_COUNT(), REGR_INTERCEPT(), REGR_R2(), REGR_SLOPE(), REGR_SXX(), REGR_SXY(), and REGR_SYY().
Non-ISO/ANSI SQL:2008 OLAP aggregate function extensions used in the database industry include FIRST_VALUE(), MEDIAN(), and LAST_VALUE().
Weighted OLAP aggregate functions that calculate weighted moving averages include EXP_WEIGHTED_AVG() and WEIGHTED_AVG().
Time series functions designed exclusively for financial time series forecasting and analysis have names beginning with “TS_”.
For more information about OLAP, see Appendix: Using OLAP in Programming.
For information on aggregate function support of the LONG BINARY and LONG VARCHAR data types, see Unstructured Data Analytics.
Aggregate function |
Parameters |
|---|---|
AVG |
( [ DISTINCT ] { column-name | numeric-expr } ) |
CORR |
(dependent-expression, independent-expression) |
COUNT |
( * ) |
COUNT |
( [ DISTINCT ] { column-name | numeric-expr } ) |
COVAR_POP |
(dependent-expression, independent-expression) |
COVAR_SAMP |
(dependent-expression, independent-expression) |
CUME_DIST |
( ) |
EXP_WEIGHTED_AVG |
(expression, period-expression) |
FIRST_VALUE |
(expression) |
LAST_VALUE |
(expression) |
LIST |
( [ DISTINCT ] string-expression [ , 'delimiter-string'] [ ORDER BY order-by-expression [ ASC | DESC ], ... ) |
MAX |
( [ DISTINCT ] { column-name | numeric-expr } ) |
MEDIAN |
(expression) |
MIN |
( [ DISTINCT ] { column-name | numeric-expr } ) |
REGR_AVGX |
(dependent-expression, independent-expression) |
REGR_AVGY |
(dependent-expression, independent-expression) |
REGR_COUNT |
(dependent-expression, independent-expression) |
REGR_INTERCEPT |
(dependent-expression, independent-expression) |
REGR_R2 |
(dependent-expression, independent-expression) |
REGR_SLOPE |
(dependent-expression, independent-expression) |
REGR_SXX |
(dependent-expression, independent-expression) |
REGR_SXY |
(dependent-expression, independent-expression) |
REGR_SYY |
(dependent-expression, independent-expression) |
STDDEV |
( [ ALL ] expression ) |
SUM |
( [ DISTINCT ] { column-name | numeric-expr } ) |
VARIANCE |
( [ ALL ] expression ) |
WEIGHTED_AVG |
(expression, period-expression |
The aggregate functions AVG, SUM, STDDEV, and VARIANCE do not support the binary data types (BINARY and VARBINARY).