Aggregate functions
Aggregate functions can be used within SELECT clause.
In line with the SQL spec:
- Except in the case of
COUNT(*), null values in the column are removed before the aggregate is calculated. - Without grouping columns, aggregate functions will always return exactly one row - if the input column is empty (after nulls have been removed), the result will be a single row containing a null value.
Numeric aggregate functions
Section titled “Numeric aggregate functions”AVG([ALL] xs)(average (mean) of all values)AVG(DISTINCT xs)(average (mean) of distinct values)COUNT([ALL] xs)(count of rows that contain non-null values)COUNT(DISTINCT xs)(count of distinct values)COUNT(*)(row count)MAX([ALL|DISTINCT] xs)(maximum value)MIN([ALL|DISTINCT] xs)(minimum value)STDDEV_POP(xs)(population standard deviation)STDDEV_SAMP(xs)(sample standard deviation)SUM([ALL] xs)(sum of values)SUM(DISTINCT xs)(sum of distinct values)VAR_POP(xs)(population variance)VAR_SAMP(xs)(sample variance)
Note:
MIN/MAXaggregates are not yet supported on string values.
Boolean aggregate functions
Section titled “Boolean aggregate functions”BOOL_AND(xs)(true if all values are true; false otherwise)BOOL_OR(xs)(false if all values are false; true otherwise)
Note: In keeping with Postgres, we rename ALL and ANY to BOOL_AND and BOOL_OR to avoid confusion with the logical operators.
Composite-type aggregate functions
Section titled “Composite-type aggregate functions”ARRAY_AGG(xs)(return an array of all of the input values)