Aggregate Functions (Aggregators)
Aggregate Functions are functions that group values of multiple rows into a single summary value. They are equivalent to aggregate functions that SQL supports (SUM, COUNT, AVG, MAX, MIN,...). For more information about how to use them, please refer to their concept page.
count
count(field)
count(table, field)
count(orders.id)
count(orders, orders.id)
// with pipe
orders | count(orders.id)
Description
Counts the total number of items in a group, not including NULL values.
Return type
Whole number
count_if
count_if(truefalse_field)
count_if(table, condition)
count_if(orders.country == 'Vietnam')
// with pipe
orders | count_if(orders.country == 'Vietnam')
Description
Counts total rows from one table that satisfy the given condition.
Return type
Whole number
count_distinct
count_distinct(field)
count_distinct(table, field)
count_distinct(orders.id)
count_distinct(orders, orders.id)
// with pipe
orders | count_distinct(orders.id)
Description
Counts the total number of distinct items in a group, not including NULL values.
Return type
Whole number
average (alias: avg)
average(field)
average(table, field)
average(orders.value)
average(orders, orders.value)
// with pipe
orders | average(orders.value)
Description
Averages the values of items in a group, not including NULL values.
Return type
Number
min
min(field)
min(table, field)
min(orders.quantity)
min(orders, orders.quantity)
// with pipe
orders | min(orders.quantity)
Description
Return the item in the group with the smallest value, not including NULL values.
Return type
Vary
max
max(field)
max(table, field)
max(order_item.quantity)
max(orders, order_item.quantity)
// with pipe
orders | max(order_item.quantity)
Description
Returns the item in the group with the largest value, not including NULL values.
Return type
Varies
sum
sum(field)
sum(table, field)
sum(order_item.quantity)
sum(order_items, order_item.quantity)
// with pipe
order_items | sum(order_item.quantity)
Description
Sums the value in the group, not including NULL values.
Return type
Number
median
median(field)
median(table, field)
median(orders.quantity)
median(orders, orders.quantity)
// with pipe
orders | median(orders.quantity)
Description
Computes the median of the values in the group, not including NULL values.
Return type
Number
stdev
stdev(field)
stdev(table, field)
stdev(orders.id)
stdev(orders, orders.id)
// with pipe
orders | stdev(orders.id)
Description
Computes the standard deviation (sample) of the values in the group, not including NULL values.
Return type
Number
stdevp
stdevp(field)
stdevp(table, field)
stdevp(orders.id)
stdevp(orders, orders.id)
// with pipe
orders | stdevp(orders.id)
Description
Computes the standard deviation (population) of the values in the group, not including NULL values.
Return type
Number
var
var(field)
var(table, field)
var(orders.id)
var(orders, orders.id)
// with pipe
orders | var(orders.id)
Description
Returns the variance (sample) of the values in the group, not including NULL values.
Return type
Number
varp
varp(field)
varp(table, field)
varp(orders.id)
varp(orders, orders.id)
// with pipe
orders | varp(orders.id)
Description
Returns the variance (population) of the values in the group, not including NULL values.
Return type
Number
corr
corr(table, field1, field2)
Description
Returns the Pearson correlation coefficient of two number fields in the table.
Return type
Number
Examples
corr(users, users.age, orders.value)
string_agg
string_agg(expression, sep: _sep, distinct: _distinct, order: _order)
string_agg(table, expression, sep: _sep, distinct: _distinct, order: _order)
Description
Returns a text that is the concatenation of all values of the expression.
Return type
Text
Examples
string_agg(products.name)
string_agg(products.name, sep: ', ')
string_agg(products.name, sep: ', ', distinct: true)
string_agg(products.name, order: 'asc')
string_agg(products.name, order: 'desc')
Parameters
expression
: A field or an AQL expression to be evaluated in each row of the table to be aggregatedtable
(optional): The table to aggregate. Only optional when the table can be inferred from the expressionsep
(optional): Separator between values, default is','
distinct
(optional): If true, only distinct values are concatenated, default isfalse
order
(optional): Specifies the ordering of values ('asc' or 'desc'), default is not specified
Notes
For SQL Server, the distinct parameter is not supported.
percentile_cont
percentile_cont(expression, percentile)
percentile_cont(table, expression, percentile)
Description
Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed.
Return type
Number
Examples
percentile_cont(ecommerce_users.age, 0.7)
ecommerce_users | percentile_cont(ecommerce_users.age, 0.7)
unique(ecommerce_users.id) | percentile_cont(total_value, 0.7)
Parameters
expression
: A field or an AQL expression to be evaluatedtable
(optional): The table to aggregate. Only optional when the table can be inferred from the expressionpercentile
: The percentile to compute. Must be a value between 0 and 1
Notes
This is not supported in the following databases:
- MySQL
- Presto/Athena
- Bigquery (it only support
percentile_disc
and window function version ofpercentile_cont
)
percentile_disc
percentile_disc(expression, percentile)
percentile_disc(table, expression, percentile)
Description
Returns the value at the given percentile of the sorted expression values. If the percentile falls between two values, a discrete value will be returned (the logic to select the value is database dependent).
Return type
Number
Examples
percentile_disc(ecommerce_users.age, 0.7)
ecommerce_users | percentile_disc(ecommerce_users.age, 0.7)
unique(ecommerce_users.id) | percentile_disc(count(ecommerce_orders.id), 0.7)
Parameters
expression
: A field or an AQL expression to be evaluatedtable
(optional): The table to aggregate. Only optional when the table can be inferred from the expressionpercentile
: The percentile to compute. Must be a value between 0 and 1
min_by
min_by(table, value, by)
Description
Returns the value of value
from the row where by
is minimum.
Return type
Varies
Examples
min_by(orders, orders.customer_name, orders.value)
Notes
This function is not supported in the following databases:
- MySQL
- PostgreSQL
- Redshift
- SQL Server
max_by
max_by(table, value, by)
Description
Returns the value of value
from the row where by
is maximum.
Return type
Varies
Examples
max_by(orders, orders.customer_name, orders.value)
Notes
This function is not supported in the following databases:
- MySQL
- PostgreSQL
- Redshift
- SQL Server