Aggregate Functions


Aggregate functions summarize the values in a column. Below are some examples of aggregate functions that can be used when retrieving data from an Oracle database. Please see the documentation for your own database for more information.

Note: The following functions only apply to data retrieved from an Oracle database:

Function

Description

SUM (%1)

total of all the rows in column %1, given that the column is numeric

AVG (%1)

average of column %1

MAX (%1)

largest figure in column %1

MIN (%1)

smallest figure in column %1

COUNT(%1)

number of rows in column %1

CORR(%1,%2)

coefficient of correlation of columns %1 and %2

COVAR_POP(%1,%2)

population covariance of columns %1 and %2

COVAR_SAMP(%1,%2)

sample covariance of columns %1 and %2

REGR_SLOPE(%1,%2)

slope of the line described by columns %1 and %2

REGR_INTERCEPT(%1,%2)

y-intercept of the regression line described by columns %1 and %2

REGR_COUNT(%1,%2)

integer that is the number of non-null number pairs used to fit the regression line

REGR_R2(%1,%2)

coefficient of determination (also called "R-squared" or "goodness of fit") for the regression

REGR_AVGX(%1,%2)

average of the independent variable (%2) of the regression line

REGR_AVGY(%1,%2)

average of the dependent variable (%1) of the regression line

REGR_SXX(%1,%2)

computed as:
REGR_COUNT(%1, %2) * VAR_POP(%2)

REGR_SYY(%1,%2)

computed as:
REGR_COUNT(%1, %2) * VAR_POP(%1)

REGR_SXY(%1,%2)

computed as:
REGR_COUNT(%1, %2) * COVAR_POP(%1, %2)

STDDEV(%1)

sample standard deviation of column %1

STDDEV_POP(%1)

computes the population standard deviation of column %1 and returns the square root of the population variance

STDDEV_SAMP(%1)

computes the cumulative sample standard deviation of column %1 and returns the square root of the sample variance

VAR_POP(%1)

population variance of column %1

VAR_SAMP(%1)

sample variance of column %1

VARIANCE(%1)

variance of column %1

See also:

Creating a Column Element

Using Aggregation