Updated on 2022-07-04 GMT+08:00

Aggregate Functions

An aggregate function performs a calculation operation on a set of input values and returns a value. For example, the COUNT function counts the number of rows retrieved by an SQL statement. Table 1 lists aggregate functions.

Table 1 Aggregate functions

Function

Return Type

Description

count(*), count(expr), count(DISTINCT expr[, expr...])

BIGINT

Return the total number of retrieved records.

sum(col), sum(DISTINCT col)

DOUBLE

Return the sum of the values in a column.

avg(col), avg(DISTINCT col)

DOUBLE

Return the average of the values in a column.

min(col)

DOUBLE

Return the minimum value of a column.

max(col)

DOUBLE

Return the maximum value of a column.

variance(col), var_pop(col)

DOUBLE

Return the variance of a numeric column.

var_samp(col)

DOUBLE

Return the sample variance of a numeric column.

stddev_pop(col)

DOUBLE

Return the deviation of a numeric column.

stddev_samp(col)

DOUBLE

Return the sample deviation of a numeric column.

covar_pop(col1, col2)

DOUBLE

Return the covariance of a pair of numeric columns.

covar_samp(col1, col2)

DOUBLE

Return the sample covariance of a pair of numeric columns.

corr(col1, col2)

DOUBLE

Return the coefficient of correlation of a pair of numeric columns.

percentile(BIGINT col, p)

DOUBLE

Return the exact pth percentile of a column. p must be between 0 and 1. Otherwise, this function returns null. This function does not work with floating point types.

percentile_approx(DOUBLE col, p [, B])

DOUBLE

Return an approximate pth percentile of a numeric column (including floating point types) in a group. p must be between 0 and 1. B controls approximation accuracy. Higher values of B mean better approximations, and the default value is 10,000. When the number of distinct values in the numeric column is smaller than B, an exact percentile value is returned.

Functions such as var_pop, stddev_pop, var_samp, stddev_samp, covar_pop, covar_samp, corr, and percentile_approx, do not support non-numeral data types, such as TimeStamp.