Aggregate Functions

Aggregates summarize rows at the query's grain, or at an explicit by grain. For a walkthrough, see context-sensitive functions. Each example's Run and Copy includes the orders model.

SUM

Computes the sum of non-null numeric values within each group. Returns NULL if the group is empty or contains only NULL values.

Inputs:

INTEGERFLOATNUMBERNUMERIC

Output:

Same as input (or promoted numeric type)

Purpose:

metric

Example:

select customer_id, sum(order_total) as revenue;

COUNT

Counts the number of non-null input rows within each group. Takes a single argument of any type.

Inputs:

Any

Output:

INTEGER

Purpose:

metric

Example:

select customer_id, count(order_id) as orders;

COUNT_DISTINCT

Counts the number of distinct non-null input values within each group. Takes a single argument of any type. Use this to count unique property values; a count of a key already has distinct-key semantics.

Inputs:

Any

Output:

INTEGER

Purpose:

metric

Example:

select count_distinct(customer_id) as customers;

MAX

Returns the maximum non-null value across all input rows within each group. Comparison follows standard data type rules.

Inputs:

INTEGERFLOATNUMBERDATEDATETIMETIMESTAMPBOOLSTRING

Output:

Same as input type

Purpose:

metric

Example:

select customer_id, max(order_total) as largest_order;

MIN

Returns the minimum non-null value across all input rows within each group. Comparison follows standard data type rules.

Inputs:

INTEGERFLOATNUMBERDATEDATETIMETIMESTAMPBOOLSTRING

Output:

Same as input type

Purpose:

metric

Example:

select customer_id, min(order_total) as smallest_order;

AVG

Computes the average (arithmetic mean) of non-null numeric values within each group. Returns NULL if the group is empty or contains only NULL values. The result is typically a FLOAT or NUMBER.

Inputs:

INTEGERFLOATNUMBERNUMERIC

Output:

FLOAT or NUMBER

Purpose:

metric

Example:

select customer_id, avg(order_total) as average_amount;

STDDEV

Computes the sample standard deviation of non-null numeric values. With fewer than two non-null values, the sample statistic is NULL.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERIC

Output:

FLOAT

Purpose:

metric

Example:

select customer_id, stddev(order_total) as amount_stddev;

VARIANCE

Computes the sample variance of non-null numeric values. With fewer than two non-null values, the sample statistic is NULL.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERIC

Output:

FLOAT

Purpose:

metric

Example:

select customer_id, variance(order_total) as amount_variance;

For customer 101's amounts of 120 and 80, sample variance is 800 and sample standard deviation is approximately 28.28. Customers 102 and 103 each have only one non-null amount, so both statistics are null.

ARRAY_AGG

Aggregates values from a group into a single array. The order of elements in the array is not guaranteed.

Inputs:

Any

Output:

ARRAY

Purpose:

metric

Example:

select customer_id, array_agg(order_id) as order_ids;

ANY

Returns a single, non-deterministic value from each group. Use it when any representative value is acceptable. It does not select the first or last value; use a window function when ordering matters.

Inputs:

Any

Output:

Same as input type

Purpose:

property

Example:

select any(order_status) by customer_id as sample_status, customer_id;

BOOL_AND

Returns TRUE if every non-null input is TRUE, FALSE if any input is FALSE, and NULL if all values are NULL.

Inputs:

BOOL

Output:

BOOL

Purpose:

metric

Example:

select customer_id, bool_and(has_shipped) as all_shipped;

BOOL_OR

Returns TRUE if any non-null input is TRUE, FALSE if all non-null inputs are FALSE, and NULL if all values are NULL.

Inputs:

BOOL

Output:

BOOL

Purpose:

metric

Example:

select customer_id, bool_or(has_shipped) as any_shipped;