Aggregate functions

Compute one result from a set of rows, such as a sum, count, average or approximate distinct count. Most can also run as window functions.

Function	Type	What it does
`any_value`	Aggregate · Window	Returns an arbitrary value from a group.
`approx_count_distinct`	Aggregate · Window	Estimates the number of distinct non-NULL input values.
`approx_percentile`	Aggregate · Window	Estimates one or more percentiles of non-NULL numeric values.
`approx_quantiles`	Being confirmed	Previous docs; being confirmed.
`approx_top_k`	Aggregate · Window	Returns the approximate most frequent input values.
`arbitrary`	Aggregate · Window	Returns an arbitrary value from a group.
`array_agg`	Aggregate · Window	Collects values into an array; non-DISTINCT calls skip NULLs, while grouped DISTINCT calls can retain one NULL.
`array_unique_agg`	Aggregate · Window	Collects distinct non-NULL values into an array.
`avg`	Aggregate · Window	Returns the arithmetic mean of non-NULL numeric values.
`bit_or`	Aggregate · Window	Returns the bitwise OR of non-NULL integer values.
`bit_xor`	Aggregate · Window	Returns the bitwise XOR of non-NULL integer values.
`bitoragg`	Aggregate · Window	Returns the bitwise OR of non-NULL integer values.
`bool_or`	Aggregate · Window	Returns true if any non-NULL input is true.
`collect_list`	Aggregate · Window	Collects values into an array; non-DISTINCT calls skip NULLs, while grouped DISTINCT calls can retain one NULL.
`collect_set`	Aggregate · Window	Collects distinct values into an array, including at most one NULL.
`corr`	Aggregate · Window	Returns the Pearson correlation coefficient.
`count`	Aggregate · Window	Counts rows or non-NULL expression values.
`count_if`	Aggregate · Window	Counts rows whose condition is true.
`countif`	Aggregate · Window	Counts rows whose condition is true.
`covar_pop`	Aggregate · Window	Returns the population covariance.
`covar_samp`	Aggregate · Window	Returns the sample covariance.
`distinct`	Being confirmed	Previous docs; being confirmed.
`every`	Aggregate · Window	Returns TRUE when every non-NULL Boolean input is TRUE; returns FALSE if any contributing input is FALSE.
`first`	Aggregate · Window	Returns the first group/frame value.
`first_value`	Aggregate · Window	Returns the first group/frame value.
`grouping`	Aggregate	Returns a number whose bits identify which GROUP BY expressions were omitted from the current grouping set.
`grouping_id`	Aggregate	Returns a number whose bits identify which GROUP BY expressions were omitted from the current grouping set.
`last`	Aggregate · Window	Returns the last group/frame value.
`last_value`	Aggregate · Window	Returns the last group/frame value.
`listagg`	Aggregate · Window	Concatenates non-NULL strings with a delimiter.
`max`	Aggregate · Window	Returns the maximum non-NULL value.
`max_by`	Aggregate · Window	Returns the value associated with the greatest non-NULL ordering key.
`mean`	Aggregate · Window	Returns the arithmetic mean of non-NULL numeric values.
`median`	Aggregate · Window	Returns the interpolated 50th percentile.
`min`	Aggregate · Window	Returns the minimum non-NULL value.
`mode`	Aggregate · Window	Returns the most frequent non-NULL value.
`object_agg`	Aggregate · Window	Combines key/VARIANT pairs into an object.
`percentile`	Aggregate · Window	Returns exact interpolated quantiles of non-NULL numeric values.
`percentile_approx`	Aggregate · Window	Estimates a percentile or an ordered array of percentiles, with accuracy controlled by the accuracy argument.
`percentile_cont`	Aggregate · Window	Returns exact interpolated quantiles of non-NULL numeric values.
`some`	Aggregate	Returns TRUE when any non-NULL Boolean input is TRUE; returns FALSE when all contributing inputs are FALSE.
`stddev`	Aggregate · Window	Returns sample standard deviation.
`stddev_pop`	Aggregate · Window	Returns population standard deviation.
`stddev_samp`	Aggregate · Window	Returns sample standard deviation.
`string_agg`	Aggregate · Window	Concatenates non-NULL strings with a delimiter.
`sum`	Aggregate · Window	Returns the sum of non-NULL numeric values.
`var_samp`	Aggregate · Window	Returns sample variance.
`variance`	Aggregate · Window	Returns sample variance.

Query Engine

Get started

Connect data

Run clusters

Query

Connect your tools

Secure and govern

Troubleshoot

Reference

Concepts