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


Let us know what you think about this document :)