AQL Cheatsheet - Functions
Aggregate Function
count()
Syntax
count([table], expression)
Description
Counts the total number of items in a group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| count(orders.id)
orders
| count(orders.id)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, total_orders: count(orders.id))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The count of non-NULL values.
Learn more
count_if()
Syntax
count_if([table], condition)
Description
Counts the total rows from one table that satisfy the given condition.
Tags
Function, Aggregate Function, Condition
Examples
orders
| count_if(orders.delivery_country == 'USA')
orders
| count_if(orders.status == 'shipped')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, shipped_orders: count_if(orders.status == 'shipped'))
Inputs
- condition (Truefalse: required): A field or an AQL expression that evaluates to true or false.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The count of rows that satisfy the condition.
Learn more
count_distinct()
Syntax
count_distinct([table], expression)
Description
Counts the total number of distinct items in a group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| count_distinct(orders.customer_id)
customers
| count_distinct(customers.id)
| where(customers.country == 'USA')
products
| group(products.id)
| select(products.id, total_countries: count_distinct(orders.delivery_country))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The count of distinct non-NULL values.
Learn more
approx_count_distinct()
Syntax
approx_count_distinct([table], expression)
Description
Counts the approximate number of distinct items in a group, excluding NULL values. Uses HyperLogLog algorithm for fast, memory-efficient approximation.
Tags
Function, Aggregate Function, Performance
Examples
orders
| approx_count_distinct(orders.user_id)
orders
| group(orders.created_at | month())
| select(
month: orders.created_at | month(),
unique_customers: approx_count_distinct(orders.customer_id)
)
orders
| approx_countd(orders.user_id)
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The approximate count of distinct non-NULL values.
Supported Databases
- Snowflake
- BigQuery
- Databricks
- MotherDuck
- Presto/Athena
Notes
- Approximation error is typically within 2-3%
- Much faster than exact count_distinct for large datasets
- MotherDuck doesn't support running totals with this function
Aliases
approx_countd()
Learn more
approx_count_distinct (AQL Doc)
average()
Syntax
average([table], expression)
Description
Calculates the average of values in a group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| average(orders.value)
orders
| average(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, avg_order_value: average(orders.value))
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The average of the values.
Aliases
avg()
Learn more
min()
Syntax
min([table], expression)
Description
Returns the item in the group with the smallest value, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| min(orders.value)
orders
| min(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, min_order_value: min(orders.value))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Any: The minimum value.
Learn more
max()
Syntax
max([table], expression)
Description
Returns the item in the group with the largest value, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| max(orders.value)
orders
| max(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, max_order_value: max(orders.value))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Any: The maximum value.
Learn more
sum()
Syntax
sum([table], expression)
Description
Calculates the sum of values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| sum(orders.value)
orders
| sum(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, total_order_value: sum(orders.value))
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The sum of the values.
Learn more
median()
Syntax
median([table], expression)
Description
Computes the median of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
median(orders.quantity)
orders
| median(orders.quantity)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The median of the values.
Learn more
stdev()
Syntax
stdev([table], expression)
Description
Computes the sample standard deviation of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
stdev(orders.value)
orders
| stdev(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The sample standard deviation.
Learn more
stdevp()
Syntax
stdevp([table], expression)
Description
Computes the population standard deviation of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
stdevp(orders.value)
orders
| stdevp(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The population standard deviation.
Learn more
var()
Syntax
var([table], expression)
Description
Returns the sample variance of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
var(orders.value)
orders
| var(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The sample variance.
Learn more
varp()
Syntax
varp([table], expression)
Description
Returns the population variance of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
varp(orders.value)
orders
| varp(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The population variance.
Learn more
string_agg()
Syntax
string_agg([table], expression, [sep: _sep], [distinct: _distinct], [order: _order])
Description
Returns a text that is the concatenation of all values of the expression.
Tags
Function, Aggregate Function
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')
Inputs
- expression (Text: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
- _sep (Text: optional): Separator between values, default is
','
. - _distinct (Truefalse: optional): If true, only distinct values are concatenated, default is
false
. - _order (Text: optional): Specifies the ordering of values ('asc' or 'desc'), default is not specified.
Output
- Text: The concatenated text of values.
Learn more
corr()
Syntax
corr(table, field1, field2)
Description
Returns the Pearson correlation coefficient of two number fields in the table.
Tags
Function, Aggregate Function
Examples
corr(users, users.age, orders.value)
Inputs
- table (Table: required): The table containing the fields to correlate.
- field1 (Number: required): The first number field to correlate.
- field2 (Number: required): The second number field to correlate.
Output
- Number: The Pearson correlation coefficient between the two fields.
Learn more
max_by()
Syntax
max_by(table, value, by)
Description
Returns the value of value
from the row where by
is maximum.
Tags
Function, Aggregate Function
Examples
max_by(orders, orders.customer_name, orders.value)
Inputs
- table (Table: required): The table to find the maximum value in.
- value (Any: required): The field to return from the row with the maximum value.
- by (Any: required): The expression to use for determining the maximum value.
Output
- Any: The value of
value
from the row whereby
is maximum.
Learn more
min_by()
Syntax
min_by(table, value, by)
Description
Returns the value of value
from the row where by
is minimum.
Tags
Function, Aggregate Function
Examples
min_by(orders, orders.customer_name, orders.value)
Inputs
- table (Table: required): The table to find the minimum value in.
- value (Any: required): The field to return from the row with the minimum value.
- by (Any: required): The expression to use for determining the minimum value.
Output
- Any: The value of
value
from the row whereby
is minimum.
Learn more
percentile_cont()
Syntax
percentile_cont([table], expression, percentile)
Description
Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed.
Tags
Function, Aggregate Function
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)
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
- percentile (Number: required): The percentile to compute. Must be a value between 0 and 1.
Output
- Any: The percentile value.
Learn more
percentile_disc()
Syntax
percentile_disc([table], expression, percentile)