Skip to content

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.
  • 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/MAX aggregates are not yet supported on string values.
  • 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.

  • ARRAY_AGG(xs) (return an array of all of the input values)