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.