Skip to main content

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)
Examples
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)
Examples
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)
Examples
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)
Examples
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)
Examples
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)
Examples
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)
Examples
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)
Examples
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)
Examples
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

Calculate the correlation between age and order value
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

Basic usage: Concatenate all product names
string_agg(products.name)
Concatenate product names with a separator
string_agg(products.name, sep: ', ')
Concatenate distinct product names with a separator
string_agg(products.name, sep: ', ', distinct: true)
Concatenate product names, ordered by name
string_agg(products.name, order: 'asc')
Concatenate product names, ordered by name (descending)
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 aggregated
  • table (optional): The table to aggregate. Only optional when the table can be inferred from the expression
  • sep (optional): Separator between values, default is ','
  • distinct (optional): If true, only distinct values are concatenated, default is false
  • 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

Calculate 70th percentile of user ages
percentile_cont(ecommerce_users.age, 0.7)
Full form usage: Calculate 70th percentile of user ages
ecommerce_users | percentile_cont(ecommerce_users.age, 0.7)
Nested aggregation: Calculate 70th percentile of total value by users
unique(ecommerce_users.id) | percentile_cont(total_value, 0.7)

Parameters

  • expression: A field or an AQL expression to be evaluated
  • table (optional): The table to aggregate. Only optional when the table can be inferred from the expression
  • percentile: 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 of percentile_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

Calculate 70th percentile of user ages
percentile_disc(ecommerce_users.age, 0.7)
Full form usage: Calculate 70th percentile of user ages
ecommerce_users | percentile_disc(ecommerce_users.age, 0.7)
Nested aggregation: Calculate 70th percentile of total orders by users
unique(ecommerce_users.id) | percentile_disc(count(ecommerce_orders.id), 0.7)

Parameters

  • expression: A field or an AQL expression to be evaluated
  • table (optional): The table to aggregate. Only optional when the table can be inferred from the expression
  • percentile: 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

Get the name of the customer with the lowest order value
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

Get the name of the customer with the highest order value
max_by(orders, orders.customer_name, orders.value)

Notes

This function is not supported in the following databases:

  • MySQL
  • PostgreSQL
  • Redshift
  • SQL Server
Let us know what you think about this document :)