Aggregates

Aggregates are functions that combine multiple rows into a single value. Aggregates can only be used in the SELECT and HAVING clauses of a SQL query.

When the ORDER BY clause is provided, the values being aggregated are sorted after applying the function.

Aggregate functions generally ignore null values when performing calculations.

Definitions noted with a are only supported in a statement with a GROUP BY clause.

New aggregates for this version are annotated with the icon.

General Functions

ANY_VALUE (column) → any

Select any single value from the grouping.

APPROXIMATE_MEDIAN (column: numeric) → numeric

Approximate median of a column with T-Digest algorithm.

ARRAY_AGG ([ DISTINCT ] column [ LIMIT n ]) → array

The list of values for column in the group.
The DISTINCT modifier optionally filters to unique values only.
The LIMIT clause limits the number of items in each list to a maximum of n items.

AVG (column: numeric) → numeric

The mean average of a numeric column.
Alias for MEAN and AVERAGE.

COUNT (*) → numeric

Count the number of rows.

COUNT (column) → numeric

Count the number of non null values in column.

COUNT_DISTINCT (column) → numeric

Count the number of unique values.

LIST (column) → array

The complete list of values for column in the group.
Related: ARRAY_AGG

MAX (column) → any

The maximum value in column.
Alias for MAXIMUM.

MIN (column) → any

The minimum value in column.
Alias for MINIMUM.

MIN_MAX (column) → struct

The minimum and maximum values in column.

ONE (column) → any

Alias for ANY_VALUE.

PRODUCT (column: numeric) → numeric

The product of values in column.

STDDEV (column: numeric) → numeric

The standard deviation of values in column.

SUM (column: numeric) → numeric

The sum of values in column.

VARIANCE (column: numeric) → numeric

The variance of values in column.