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.