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.

COUNT (*) → numeric

Count the number of rows.

COUNT ([ DISTINCT ] column) → numeric

Count the number of non null values in column.
Optionally accepts DISTINCT as a modifier to count unique values, note that DISTINCT includes null in counts.

COUNT_DISTINCT (column) → numeric

Count the number of unique values.
Alias of COUNT(DISTINCT column)

HISTOGRAM (column) → struct

An histogram of values in column.

MAX (column) → any

The maximum value in column.

MIN (column) → any

The minimum value in column.

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.