Skip to content
Atoll Book

Aggregates

Group SQL rows with GROUP BY and HAVING, and compute checked aggregate projections.

Updated View as Markdown

An aggregate function reduces many rows to one value. GROUP BY decides how the rows are partitioned before that happens, and HAVING filters the groups afterwards.

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

fn spend_per_customer(): []{
    customer_id: int,
    orders: int,
    spend: float,
} ! QueryError {
    return FROM Order
        GROUP BY customer_id
        HAVING count() > 1
        SELECT {
            customer_id,
            orders: count(),
            spend: sum(total),
        }?
}

fn main(): void {
    match spend_per_customer() {
        Ok(rows) => {
            for row in rows {
                println("customer ${row.customer_id}: ${row.orders} orders, ${row.spend}")
            }
        }
        Err(e) => println("query failed")
    }
}

The result row is one row per distinct customer_id, not one row per order. customer_id is legal in the projection because it is a grouping key; count() and sum(total) are legal because they are aggregates.

Aggregating without grouping

Leave GROUP BY out and the whole table is one group, producing exactly one row:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn overview(): []{
    orders: int,
    revenue: float,
    average: float,
    smallest: float,
    largest: float,
} ! QueryError {
    return FROM Order
        SELECT {
            orders: count(),
            revenue: sum(total),
            average: avg(total),
            smallest: min(total),
            largest: max(total),
        }?
}

fn main(): void {
    match overview() {
        Ok(rows) => {
            for row in rows {
                println("${row.orders} orders, ${row.revenue} total")
                println("avg ${row.average}, min ${row.smallest}, max ${row.largest}")
            }
        }
        Err(e) => println("query failed")
    }
}

Every one of those columns is typed non-optional. sum, avg, min, and max over zero rows are SQL nulls, but the checker still types them as float, so declaring the field optional to model the empty case is a hard type error:

schema shop

model Order {
    @id
    id: int
    total: float
}

// `sum(total)` is typed `float`, never `float?`.
fn broken(): []{ revenue: float? } ! QueryError {
    return FROM Order SELECT { revenue: sum(total) }?
}

Project count() alongside the other aggregates and check it before trusting them; that is the portable way to tell “the total is zero” from “there was nothing to total”.

Single-row collapse

Iterating a list that always has exactly one element is awkward. When a projection contains only aggregates and there is no GROUP BY, a struct-typed context collapses the usual list carrier into that struct directly:

schema shop

model Order {
    @id
    id: int
    total: float
}

struct Overview {
    orders: int
    revenue: float
    average: float
}

fn overview(): Overview ! QueryError {
    return FROM Order
        SELECT {
            orders: count(),
            revenue: sum(total),
            average: avg(total),
        }?
}

fn main(): void {
    match overview() {
        Ok(o) => println("${o.orders} orders worth ${o.revenue} (avg ${o.average})")
        Err(e) => println("query failed")
    }
}

Field names and types must line up with the struct exactly. Collapse applies only when every projected value is an aggregate and there is no GROUP BY; add a plain column or a grouping key and the result is a list again.

The struct type has to reach the query from the context — a return type as above, or an annotation on the binding. A bare := gets the list:

schema shop

model Order {
    @id
    id: int
    total: float
}

struct Overview {
    orders: int
    revenue: float
}

fn annotated(): string ! QueryError {
    // Collapsed: the annotation supplies the struct context.
    o: Overview = FROM Order SELECT { orders: count(), revenue: sum(total) }?

    // Not collapsed: `rows` is a one-element list of anonymous records.
    rows := FROM Order SELECT { orders: count(), revenue: sum(total) }?

    return "${o.orders} orders, ${rows.len()} row(s) in the uncollapsed read"
}

fn main(): void {
    match annotated() {
        Ok(line) => println(line)
        Err(e) => println("query failed")
    }
}

The built-in aggregates

Call Result
count() number of rows in the group, as int
count_distinct(col) number of distinct non-null values
sum(col) sum, in the column’s numeric type
avg(col) arithmetic mean, as float
min(col) / max(col) extreme value, in the column’s type

count() takes no argument — it counts rows, including rows where every other column is null. count_distinct takes exactly the column whose distinct values you want:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    status: string
    total: float
}

fn distinct_buyers(): []{ status: string, buyers: int } ! QueryError {
    return FROM Order
        GROUP BY status
        SELECT { status, buyers: count_distinct(customer_id) }?
}

fn main(): void {
    match distinct_buyers() {
        Ok(rows) => {
            for row in rows {
                println("${row.status}: ${row.buyers} distinct buyers")
            }
        }
        Err(e) => println("query failed")
    }
}

avg widens to float even over an integer column, which is why the Overview struct above declares average: float while revenue keeps the column’s own type.

Every projected column must be grouped or aggregated

This is the rule that catches people. A bare column in the projection has no single value across a group, so the compiler rejects it:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn broken(): void ! QueryError {
    rows := FROM Order
        GROUP BY customer_id
        SELECT { customer_id, total }?
    println("${rows.len()}")
}

That is ATOLL3240: column 'total' must appear in GROUP BY or be used in an aggregate function. Either wrap it — spend: sum(total) — or add it to the grouping keys. Several keys are comma-separated, and every one of them may appear bare in the projection:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    status: string
    total: float
}

fn grid(): []{
    customer_id: int,
    status: string,
    orders: int,
    spend: float,
} ! QueryError {
    return FROM Order
        GROUP BY customer_id, status
        SELECT {
            customer_id,
            status,
            orders: count(),
            spend: sum(total),
        }?
}

fn main(): void {
    match grid() {
        Ok(rows) => {
            for row in rows {
                println("${row.customer_id}/${row.status}: ${row.orders} × ${row.spend}")
            }
        }
        Err(e) => println("query failed")
    }
}

WHERE versus HAVING

The two filters run at different stages, and swapping them changes the answer rather than just the plan:

  1. FROM and joins produce source rows;
  2. WHERE discards source rows;
  3. GROUP BY partitions what survives;
  4. aggregates compute one value per group;
  5. HAVING discards whole groups;
  6. SELECT builds the result rows;
  7. ORDER BY, LIMIT, and OFFSET shape the delivery.

So WHERE status == "settled" decides which orders count towards the sum, while HAVING sum(total) > 500.0 decides which customers appear at all:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    status: string
    total: float
}

fn valuable_customers(
    floor: float,
): []{ customer_id: int, spend: float } ! QueryError {
    return FROM Order
        WHERE status == "settled"
        GROUP BY customer_id
        HAVING sum(total) >= floor
        SELECT { customer_id, spend: sum(total) }
        ORDER BY spend DESC
        LIMIT 10?
}

fn main(): void {
    match valuable_customers(500.0) {
        Ok(rows) => {
            for row in rows {
                println("customer ${row.customer_id} spent ${row.spend}")
            }
        }
        Err(e) => println("query failed")
    }
}

An aggregate in WHERE is rejected outright, because at that stage no group exists yet:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn broken(): void ! QueryError {
    rows := FROM Order
        WHERE count() > 2
        SELECT { id }?
    println("${rows.len()}")
}

HAVING accepts the same if clause guard as WHERE, so an optional group filter does not need a second query:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn spend(
    floor: float,
    apply_floor: bool,
): []{ customer_id: int, spend: float } ! QueryError {
    return FROM Order
        GROUP BY customer_id
        HAVING sum(total) >= floor if apply_floor
        SELECT { customer_id, spend: sum(total) }?
}

fn main(): void {
    match spend(500.0, false) {
        Ok(rows) => println("unfiltered: ${rows.len()} customers")
        Err(e) => println("query failed")
    }
    match spend(500.0, true) {
        Ok(rows) => println("filtered: ${rows.len()} customers")
        Err(e) => println("query failed")
    }
}

The guard is evaluated when the statement is built, not rendered into SQL text.

Aggregating a join

Aggregates read qualified columns, which is how you summarise across two models. Group by the parent’s key and the many side collapses:

schema shop

model Customer {
    @id
    id: int
    name: string
    region: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn revenue_by_region(): []{
    region: string,
    customers: int,
    revenue: float,
} ! QueryError {
    return FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        GROUP BY Customer.region
        SELECT {
            region: Customer.region,
            customers: count_distinct(Customer.id),
            revenue: sum(Order.total),
        }
        ORDER BY revenue DESC?
}

fn main(): void {
    match revenue_by_region() {
        Ok(rows) => {
            for row in rows {
                println("${row.region}: ${row.customers} customers, ${row.revenue}")
            }
        }
        Err(e) => println("query failed")
    }
}

Declaring your own aggregate

@sql.aggregate registers a compiler-known aggregate backed by an SQL template. The declaration is a body-less fn; $1, $2, … are the argument holes:

schema shop

model Review {
    @id
    id: int
    product_id: int
    rating: float
    helpful_votes: float
}

@sql.aggregate("SUM($1 * $2) / NULLIF(SUM($2), 0)")
fn weighted_avg(value: float, weight: float): float

fn product_scores(floor: float): []{ product_id: int, score: float } ! QueryError {
    return FROM Review
        GROUP BY product_id
        HAVING weighted_avg(rating, helpful_votes) >= floor
        SELECT { product_id, score: weighted_avg(rating, helpful_votes) }?
}

fn main(): void {
    match product_scores(3.5) {
        Ok(rows) => {
            for row in rows {
                println("product ${row.product_id} scores ${row.score}")
            }
        }
        Err(e) => println("query failed")
    }
}

Because the compiler knows it is an aggregate, weighted_avg(rating, ...) is legal in a projection under GROUP BY without rating being a grouping key, and legal in HAVING — as above. It is rejected in WHERE, exactly like the built-ins.

The declared Atoll return type is what the row decoder trusts after compilation, so it must match every engine’s rendering of the template. A second decorator can give one engine a different spelling of the same function.

@sql.function declares a scalar the same way. It is not an aggregate, so it collapses nothing and may appear in WHERE:

schema shop

model Order {
    @id
    id: int
    reference: string
    total: float
}

@sql.function("UPPER($1)")
fn shout(text: string): string

fn shouted(): []{ id: int, reference: string } ! QueryError {
    return FROM Order
        WHERE shout(reference) == "AB-100"
        SELECT { id, reference: shout(reference) }?
}

fn main(): void {
    match shouted() {
        Ok(rows) => println("${rows.len()} matches")
        Err(e) => println("query failed")
    }
}

A worked example

schema shop

model Customer {
    @id
    id: int
    name: string
    region: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

struct Totals {
    orders: int
    revenue: float
}

/// The grand total across every settled order.
fn settled_totals(): Totals ! QueryError {
    return FROM Order
        WHERE status == "settled"
        SELECT { orders: count(), revenue: sum(total) }?
}

/// One line per region that cleared `min_revenue`, biggest first.
fn regional_lines(min_revenue: float): []{
    region: string,
    orders: int,
    revenue: float,
    largest: float,
} ! QueryError {
    return FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        WHERE Order.status == "settled"
        GROUP BY Customer.region
        HAVING sum(Order.total) >= min_revenue
        SELECT {
            region: Customer.region,
            orders: count(),
            revenue: sum(Order.total),
            largest: max(Order.total),
        }
        ORDER BY revenue DESC?
}

fn main(): void {
    match settled_totals() {
        Ok(t) => println("settled: ${t.orders} orders, ${t.revenue} revenue")
        Err(e) => println("totals failed")
    }
    match regional_lines(1000.0) {
        Ok(rows) => {
            for row in rows {
                println("${row.region}: ${row.orders} orders, ${row.revenue} revenue, biggest ${row.largest}")
            }
        }
        Err(e) => println("regional report failed")
    }
}

Note the return type on settled_totals. Collapse is driven by the checked context, so a bare := inside the function would still yield a []{ ... }. regional_lines is a list either way, because region is a grouping key.

One shape to avoid: a void ! QueryError function that loops over its own query result does not currently lower (ATOLL5005). Return the rows and iterate in the caller, as above.

Window functions compute a value per row without collapsing groups; they live in Advanced.

Navigation

Type to search…

↑↓ navigate↵ selectEsc close