Aggregate Functions

Aggregate functions operate on values across rows to perform operations such as counts, min/max values, average values, standard deviation, mathematical operations, and estimation.

An aggregate function takes multiple (0, 1, or more) rows as input, and produces a single output. For example, consider the following table named table_x_y:

+-----+-----+
|  X  |  Y  |
|-----|-----|
|  10 |  20 |
|  20 |  44 |
|  30 |  70 |
+-----+-----+

The following SQL statement, which uses the AVG() aggregate function, returns the average value of column X across all rows in the table:

SELECT AVG(x) FROM table_x_y;

The output is:

+--------+
| AVG(X) |
|--------|
|     20 |
+--------+

(Aggregate functions differ from scalar functions, such as COS() (cosine), which take one row as input and produce one row (one value) as output.)

Note

Most general aggregate functions can also serve as window 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, which can optionally include a window frame.

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 window frames in the ORDER BY subclause.
COVAR_POP  
COVAR_SAMP  
HASH_AGG  
LISTAGG  
MAX Supports window frames in the ORDER BY subclause.
MEDIAN  
MIN Supports window frames in the ORDER BY subclause.
PERCENTILE_CONT  
PERCENTILE_DISC  
STDDEV Alias for STDDEV_POP.
STDDEV_POP  
STDDEV_SAMP  
SUM Supports window frames 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  
Frequency Estimation (Using Space-Saving) APPROX_TOP_K  
APPROX_TOP_K_ACCUMULATE  
APPROX_TOP_K_COMBINE  
APPROX_TOP_K_ESTIMATE  
Percentile Estimation (Using t-Digest) APPROX_PERCENTILE  
APPROX_PERCENTILE_ACCUMULATE  
APPROX_PERCENTILE_COMBINE  
APPROX_PERCENTILE_ESTIMATE  

The following table lists aggregate functions, and indicates which can be used as window functions and which can be used as window frame functions.

Function Name Window Window Frame Footnotes
ANY_VALUE    
APPROX_PERCENTILE_ACCUMULATE    
APPROX_PERCENTILE_COMBINE     1
APPROX_PERCENTILE_ESTIMATE     1
APPROX_TOP_K    
APPROX_TOP_K_ACCUMULATE    
APPROX_TOP_K_COMBINE     1
APPROX_TOP_K_ESTIMATE     1
APPROXIMATE_SIMILARITY     1
ARRAY_AGG    
AVG    
BITAND_AGG    
BITOR_AGG    
BITXOR_AGG    
CORR    
COUNT  
COVAR_POP    
COVAR_SAMP    
HASH_AGG    
HLL    
HLL_ACCUMULATE    
HLL_COMBINE     1
HLL_ESTIMATE     1
LISTAGG    
MAX  
MEDIAN    
MIN  
MINHASH    
MINHASH_COMBINE     1
OBJECT_AGG    
PERCENTILE_CONT   2
PERCENTILE_DISC   2
REGR_AVGX    
REGR_AVGY    
REGR_COUNT    
REGR_INTERCEPT    
REGR_R2    
REGR_SLOPE    
REGR_SXX    
REGR_SXY    
REGR_SYY    
STDDEV    
STDDEV_SAMP    
SUM  
VARIANCE_POP    
VARIANCE_SAMP    

Footnotes:

  1. “_COMBINE” and “_ESTIMATE” functions are special cases. Their inputs are from the corresponding _ACCUMULATE function (or equivalent), not from a table. These functions are listed here for completeness, but for all practical purposes you can think of them as not being aggregate functions.
  2. The PERCENTILE_CONT and PERCENTILE_DISC functions use a different syntax.
  3. WIDTH_BUCKET can be used somewhat like an analytic function, but it is not a true analytic function and cannot use the windowing syntax that true analytic/window functions use.