Aggregate Functions

Aggregate functions operate on values across rows to perform operations such as counts, min/max values, average values, deviations, mathematical operations, and estimation of cardinality and similarity.

Note

Most general aggregate functions can be called as analytic functions by adding an OVER clause. The OVER clause enables performing aggregations over a defined group of rows, often referred to as a window. In addition, certain aggregate functions support an ORDER BY subclause in the window. For these functions, a window frame can also be specified.

Sub-category Function Notes
Aggregation Utilities GROUPING Does not perform aggregation; instead, can be used in conjunction with aggregation functions on rows produced by a GROUP BY query.
GROUPING_ID Alias for GROUPING.
General Aggregation ANY_VALUE  
AVG  
CORR  
COUNT Supports a window frame in the ORDER BY subclause.
COVAR_POP  
COVAR_SAMP  
HASH_AGG  
LISTAGG  
MAX Supports a window frame in the ORDER BY subclause.
MEDIAN  
MIN Supports a window frame in the ORDER BY subclause.
PERCENTILE_CONT  
PERCENTILE_DISC  
STDDEV Alias for STDDEV_POP.
STDDEV_POP  
STDDEV_SAMP  
SUM Supports a window frame in the ORDER BY subclause.
VAR_POP  
VAR_SAMP  
VARIANCE_POP Alias for VAR_POP.
VARIANCE , VARIANCE_SAMP Aliases for VAR_SAMP.
Bitwise Aggregation BITAND_AGG  
BITOR_AGG  
BITXOR_AGG  
Semi-structured Data Aggregation ARRAY_AGG  
OBJECT_AGG  
Linear Regression REGR_AVGX  
REGR_AVGY  
REGR_COUNT  
REGR_INTERCEPT  
REGR_R2  
REGR_SLOPE  
REGR_SXX  
REGR_SXY  
REGR_SYY  
Cardinality Estimation (Using HyperLogLog) APPROX_COUNT_DISTINCT Alias for HLL.
HLL  
HLL_ACCUMULATE  
HLL_COMBINE  
HLL_ESTIMATE  
HLL_EXPORT  
HLL_IMPORT  
Similarity Estimation (Using MinHash) APPROXIMATE_JACCARD_INDEX Alias for APPROXIMATE_SIMILARITY.
APPROXIMATE_SIMILARITY  
MINHASH  
MINHASH_COMBINE