Skip to content
XTDB

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

  • 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.

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

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