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. See “Analytical functions”.
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 in conjunction with a SQL query specification (or window-spec). The time series window aggregate functions calculate correlation, linear regression, ranking, and weighted average results:
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-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().
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) |
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).
See the individual analytical function descriptions in this chapter for specific details on the use of each function.
For more information about using OLAP functions, see Chapter 2, “Using OLAP” in the System Administration Guide: Volume 2.
