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 using information linkis. 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