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 an ORDER BY clause is provided within an aggregate function, the input values are sorted before being aggregated.

Aggregate functions generally ignore null values when performing calculations.

Definitions noted with a are only supported in statements that include a GROUP BY clause.

New aggregates for this version are annotated with the icon.

General Functions

ANY_VALUE (column) → any

Select an arbitrary single value from column within the grouping. The specific value returned is not guaranteed.

APPROXIMATE_MEDIAN (column: numeric) → numeric

Calculate an approximate median of column using the T-Digest algorithm. This is faster than computing the exact median for large datasets.

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

Collect all values of column within the group into an array.
The DISTINCT modifier optionally filters the array to contain only unique values.
The LIMIT clause restricts the array to a maximum of n items.

AVG (column: numeric) → numeric

Calculate the arithmetic mean (average) of values in column.

COUNT (*) → numeric

Count the total number of rows.

COUNT ([ DISTINCT ] column) → numeric

Count the number of non-null values in column.
The DISTINCT modifier counts only unique values. Note that when using DISTINCT, null values are included in the count if present.

COUNT_DISTINCT (column) → numeric

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

HISTOGRAM (column) → struct

Generate a histogram showing the frequency distribution of values in column.

MAX (column) → any

The maximum value in column.

MIN (column) → any

The minimum value in column.

MIN_MAX (column) → struct

Return both the minimum and maximum values in column as a struct.

ONE (column) → any

Alias for ANY_VALUE.

PRODUCT (column: numeric) → numeric

Calculate the product (multiplication) of all values in column.

STDDEV (column: numeric) → numeric

Calculate the standard deviation of values in column.

SUM (column: numeric) → numeric

Calculate the sum (total) of all values in column.

VARIANCE (column: numeric) → numeric

Calculate the variance of values in column.