SQL Reference > Aggregate Functions

Aggregate Functions

This page covers functions that can be used in aggregation queries.

All aggregate functions take arguments which are expressions potentially involving an identifier. The functions operate on a number of values and return a single result. All support a DISTINCT option, as in COUNT(DISTINCT x), where all the duplicate values are removed from the input set.

> SELECT COUNT(is_even) FROM numbers
42
> SELECT COUNT(DISTINCT is_even) FROM numbers
2

General Aggregations

APPROX_DISTINCT

APPROX_DISTINCT(x)

Returns the approximate number of distinct elements in x. Calculated using HyperLogLog algorithm. Use COUNT(DISTINCT x) for an exact count.

ARBITRARY

ARBITRARY(x)

Returns an arbitrary non-null element in x.

ARRAY_AGG

ARRAY_AGG(x)

Returns an array created from all the elements in x.

COUNT

COUNT(x)

Returns the number of non-null elements in x.

COUNT(*)

Returns the number of input rows.

GROUPING

GROUPING(col1, col1, ... col_n)

Returns the grouping mask, which is a bitmask associating one bit with every column (the first column in the list of arguments corresponds to the most significant bit in the result).

A column’s bit is 0 if the column is included in the current grouping and 1 if it is not included.

Most useful with GROUPING SETS, ROLLUP, or CUBE.

Logical Aggregations

BITWISE_AND_AGG

BITWISE_AND_AGG(x)

Returns the bitwise AND of all input values in 2’s complement representation.

BITWISE_OR_AGG

BITWISE_OR_AGG(x)

Returns the bitwise OR of all input values in 2’s complement representation.

BOOL_AND

BOOL_AND(x)

Returns true if every value in the input is true, false otherwise. Return value and all arguments are boolean.

BOOL_OR

BOOL_OR(x)

Returns true if one value in the input is true, false otherwise. Return value and all arguments are boolean.

COUNT_IF

COUNT_IF(x)

Returns the number of elements in x which are true.

EVERY

EVERY(x)

An alias of BOOL_AND function.

Mathematical Aggregations

AVG

AVG(x)

Returns an average of all the elements in x. All elements are implicitly promoted to float. Return value is of type float.

GEOMETRIC_MEAN

GEOMETRIC_MEAN(x)

Returns the geometric mean of elements in x. All elements are implicitly promoted to float. Return value is of type float.

MAX

MAX(x)

Returns the maximum value of all elements in x.

MAX_BY

MAX_BY(x, y)

Returns the value of column x associated with the maximum value of column y.

MIN

MIN(x)

Returns the minimum value of all elements in x.

MIN_BY

MIN_BY(x, y)

Returns the value of column x associated with the minimum value of column y.

STDDEV_SAMP

STDDEV_SAMP(x)

Returns the sample standard deviation of all input values.

SUM

SUM(x)

Returns the sum of all elements in x. Returns a value of type int if all of the input elements are int, float otherwise.