Context-sensitive functions
Context-sensitive functions
Aggregate functions summarize values at a grain: the keys or dimensions that identify one result row. An unbound aggregate such as sum(order_total) inherits its grain from the surrounding query. An explicit sum(order_total) by customer_id fixes the calculation at customer grain.
Keep reusable metrics unbound when they should answer different questions in different queries. Use an explicit grain for a specific entity property, such as a customer's lifetime revenue.
An aggregate's answer depends on which dimensions the query selects. Window functions also depend on surrounding rows, with an explicit partition and ordering. Both are context-sensitive; scalar functions such as round transform each input value, while row-producing functions such as unnest create rows. See the function directory for all three groups.
A small orders model
Every Run button includes this model automatically. Copy includes the model and the query, so each example also works in a fresh DuckDB session. Download the model to reuse it locally.
Show the orders model
key order_id int;
property order_id.customer_id int;
property order_id.order_total float?;
property order_id.order_status string;
property order_id.has_shipped bool?;
datasource example_orders (
order_id: order_id,
customer_id: customer_id,
order_total: order_total,
order_status: order_status,
has_shipped: has_shipped
)
grain (order_id)
query '''
select 1 as order_id, 101 as customer_id, 120.0 as order_total,
'paid' as order_status, true as has_shipped
union all select 2, 101, 80.0, 'paid', false
union all select 3, 102, 50.0, 'paid', true
union all select 4, 102, null, 'cancelled', null
union all select 5, 103, 50.0, 'pending', false
''';
Each order has its own key, even when two orders have the same amount. customer_id is a property of an order in this small model; several orders can share it. Nullable declarations (float? and bool?) allow the missing values in order 4.
Let the query choose the grain
Define a reusable metric, then select it alone or alongside a dimension:
metric revenue <- sum(order_total);
select revenue;
metric revenue <- sum(order_total);
select customer_id, revenue
order by customer_id;
The first query returns one row with revenue 300. The second returns:
| customer_id | revenue |
|---|---|
| 101 | 200 |
| 102 | 50 |
| 103 | 50 |
Adding order_status to the second select would calculate revenue for each customer/status combination. Selecting order_id would take the result down to individual orders. Trilogy infers these groups from the selected non-aggregate concepts; ordinary grouped queries do not need SQL's GROUP BY.
Fix the grain with by
Put by immediately after the aggregate, before its alias. by * requests a single total across all input rows.
auto customer_revenue <- sum(order_total) by customer_id;
auto all_revenue <- sum(order_total) by *;
select
order_id,
customer_id,
order_total,
customer_revenue,
all_revenue
order by order_id;
| order_id | customer_id | order_total | customer_revenue | all_revenue |
|---|---|---|---|---|
| 1 | 101 | 120 | 200 | 300 |
| 2 | 101 | 80 | 200 | 300 |
| 3 | 102 | 50 | 50 | 300 |
| 4 | 102 | NULL | 50 | 300 |
| 5 | 103 | 50 | 50 | 300 |
The fixed customer total repeats on that customer's orders. It does not turn into a per-order sum when order_id is selected. Avoid summing these repeated totals as though they were separate order amounts.
You can combine grains to calculate a share of the overall total:
select
customer_id,
sum(order_total) as revenue,
100.0 * sum(order_total) / nullif(sum(order_total) by *, 0) as revenue_pct
order by customer_id;
Customers 101, 102, and 103 contribute approximately 66.67%, 16.67%, and 16.67%. nullif(..., 0) makes a zero denominator null.
Count orders, populated values, and distinct values
select
count(order_id) as orders,
count(order_total) as orders_with_amount,
count_distinct(order_total) as distinct_amounts,
count_distinct(customer_id) as customers,
avg(order_total) as average_order_amount;
| orders | orders_with_amount | distinct_amounts | customers | average_order_amount |
|---|---|---|---|---|
| 5 | 4 | 3 | 3 | 75 |
count(order_id) counts order keys. count(order_total) counts non-null amounts, including both orders worth 50. count_distinct(order_total) counts the three different amounts: 50, 80, and 120. A count of a key already has distinct-key semantics; use count_distinct when deduplicating property values.
avg divides by the number of non-null values. It does not treat the missing amount as zero: 300 / 4 = 75.
Filter the input to an aggregate
A query's where restricts the input population. To compare a subset with the full population in the same query, filter the aggregate's argument:
select
sum(order_total) as all_revenue,
sum(filter order_total where order_status = 'paid') as paid_revenue,
count(filter order_id where order_status = 'paid') as paid_orders;
This returns 300, 250, and 3. The paid filter affects those two calculations; it does not remove the pending order from all_revenue.
Use having to filter the resulting groups:
select
customer_id,
sum(order_total) as revenue
having revenue > 100
order by revenue desc;
Only customer 101 remains, with revenue 200. A fixed by grain controls grouping; it does not by itself exempt a calculation from query filters.
Nulls, empty inputs, and collection results
sum, avg, min, and max ignore null inputs. If there are no non-null values to summarize, they return null. count and count_distinct return zero for an empty input.
where order_status = 'cancelled'
select
count(order_id) as cancelled_orders,
count(order_total) as known_amounts,
sum(order_total) as cancelled_revenue,
coalesce(sum(order_total), 0.0) as cancelled_revenue_or_zero;
The result is 1, 0, NULL, and 0. Apply coalesce when zero is the intended business meaning for a missing total. An aggregate cannot invent missing dimension groups; use a spine or dimension datasource when you need rows for groups with no facts.
select
customer_id,
array_sort(array_agg(order_id)) as order_ids,
bool_and(has_shipped) as all_shipped,
bool_or(has_shipped) as any_shipped
order by customer_id;
| customer_id | order_ids | all_shipped | any_shipped |
|---|---|---|---|
| 101 | [1, 2] | false | true |
| 102 | [3, 4] | true | true |
| 103 | [5] | false | false |
The boolean aggregates ignore nulls: customer 102 has one true value and one null. An all-null boolean group returns null. array_agg does not guarantee element order, so use array_sort when a sorted result matters. Null handling inside arrays depends on the target database.
Rank orders within each customer
# Keep the highest-value order for each customer.
where order_total is not null
select
order_id,
customer_id,
order_total,
row_number order_id over customer_id
by order_total desc, order_id asc as order_position
having order_position = 1
order by customer_id;
| order_id | customer_id | order_total | order_position |
|---|---|---|---|
| 1 | 101 | 120 | 1 |
| 3 | 102 | 50 | 1 |
| 5 | 103 | 50 | 1 |
where removes null amounts before ranking; having filters the calculated window result. The order ID breaks ties, so row_number chooses one order per customer predictably. To keep every order tied for the highest amount, use rank order_id over customer_id by order_total desc and filter rank 1. Do not add the order ID as a ranking tie-breaker if ties should share a rank.
Compare neighboring orders
select
order_id,
customer_id,
order_total,
lag 1 order_total over customer_id by order_id asc as previous_amount
order by customer_id, order_id;
Order 2 has previous amount 120; order 4 has previous amount 50. The first order for each customer has no predecessor, so its previous amount is null. Window ordering determines the calculation, while the final order by determines how the result is displayed.
For function signatures, use the aggregate and window references. For multi-level totals, see grouping sets.