Updated on 2022-07-12 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([ ALL ] expression | DISTINCT expression1 [, expression2]*)

BIGINT

Returns the number of input rows for which the expression is not NULL. Use DISTINCT for one unique instance of each value.

COUNT(*)

COUNT(1)

BIGINT

Returns the number of input rows.

AVG([ ALL | DISTINCT ] expression)

DOUBLE

Returns the average (arithmetic mean) of expression across all input rows.

Use DISTINCT for one unique instance of each value.

SUM([ ALL | DISTINCT ] expression)

DOUBLE

Returns the sum of expression across all input rows.

Use DISTINCT for one unique instance of each value.

MAX([ ALL | DISTINCT ] expression)

DOUBLE

Returns the maximum value of expression across all input rows.

MIN([ ALL | DISTINCT ] expression)

DOUBLE

Returns the minimum value of expression across all input rows.

STDDEV_POP([ ALL | DISTINCT ] expression)

DOUBLE

Returns the population standard deviation of expression across all input rows.

STDDEV_SAMP([ ALL | DISTINCT ] expression)

DOUBLE

Returns the sample standard deviation of expression across all input rows.

VAR_POP([ ALL | DISTINCT ] expression)

DOUBLE

Returns the population variance (square of the population standard deviation) of expression across all input rows.

VAR_SAMP([ ALL | DISTINCT ] expression)

DOUBLE

Returns the sample variance (square of the sample standard deviation) of expression across all input rows.

COLLECT([ ALL | DISTINCT ] expression)

MULTISET

Returns a multiset of expression across all input rows.

VARIANCE([ ALL | DISTINCT ] expression)

DOUBLE

Returns the sample variance (square of the sample standard deviation) of expression across all input rows.

FIRST_VALUE(expression)

Actual type

Returns the first value in an ordered set of values.

LAST_VALUE(expression)

Actual type

Returns the last value in an ordered set of values.