Aggregate functions
Aggregate functions can be used within the aggregate
clause in XTQL, or the SELECT
clause in SQL.
In line with the SQL spec (which XTDB also respects in XTQL queries):
-
Except in the case of
(row-count)
/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
XTQL | SQL | |
---|---|---|
|
|
average (mean) |
|
|
average (mean) of distinct values |
|
|
count |
|
|
count of distinct values |
|
|
row count |
|
|
maximum |
|
|
maximum of distinct values |
|
|
minimum |
|
|
minimum of distinct values |
|
|
population standard deviation |
|
|
sample standard deviation |
|
|
sum |
|
|
sum of distinct values |
|
|
population variance |
|
|
sample variance |
-
min
/max
aggregates are not yet supported on string values.
Boolean aggregate functions
XTQL | SQL | |
---|---|---|
|
|
true if all values are true; false otherwise |
|
|
false if all values are false; true otherwise |
Composite-type aggregate functions
XTQL | SQL | |
---|---|---|
|
|
return an array of all of the input values |