Aggregate functions

Function

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.

Usage

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.”

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:

Table 4-1: Aggregate functions

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 also

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.