Union and Grouping

Union and grouping are relational operations with dedicated Trilogy syntax, not ordinary scalar function calls.

Stack rows

Use the relational union(...) table-valued function:

with combined as union(
    (where source = 'active'
     select customer_id as customer_id, amount as amount),
    (where source = 'lapsed'
     select customer_id as customer_id, amount as amount)
) -> (customer_id, amount);

See Table-Valued and Row-Producing Functions for positional output rules, re-aggregation, unnest(...), and date_spine(...).

Set an aggregate grain

Aggregates inherit the select grain unless an explicit by is attached:

select
    customer.id,
    sum(order.revenue) as customer_revenue,
    sum(order.revenue) by order.category as category_revenue,
;

Use by * for a whole-dataset aggregate.

Calculate multiple grouping levels

Use a select-level by rollup, by cube, or by grouping sets clause for subtotals and grand totals:

select
    order.region,
    order.category,
    sum(order.revenue) as revenue,
by rollup (order.region, order.category);

See ROLLUP, CUBE, and Grouping Sets for the complete syntax.