Aggregates

Aggregate functions reduce rows. GROUP BY produces one result group per key; HAVING filters those groups.

totals := FROM Booking
    GROUP BY customer_id
    HAVING count() > 1
    SELECT {
        customer_id
        bookings: count()
        revenue: sum(total)
    }?

Every selected non-aggregate column must be compatible with the grouping keys.

Grouping changes the result row from source-model rows to one row per distinct key tuple. A projection should name every aggregate so its result contract is clear.

Built-ins

The portable aggregate corpus includes count, count_distinct, sum, avg, min, and max, with return types determined from the argument and engine rules. Additional aggregates may render only on selected engines.

count() counts rows. count_distinct(value) counts distinct non-null values. Other aggregates follow the selected engine’s empty-set and null rules, so use an optional or checked target where the aggregate can have no value.

stats := FROM Booking
    SELECT {
        count: count()
        average: avg(total)
        highest: max(total)
    }?

Single-row collapse

A projection containing only aggregates and no GROUP BY produces exactly one database row. In a matching checked context, the compiler can collapse the usual list carrier to a struct or record:

struct BookingStats {
    total: int
    revenue: float
}

fn booking_stats(): BookingStats ! QueryError {
    return FROM Booking
        SELECT {
            total: count()
            revenue: sum(price)
        }?
}

Field names and types must match the checked target. Without that struct-shaped context, the inferred query result remains a list of projection rows.

Collapse applies only when there is no GROUP BY and every projected value is an aggregate. Adding a plain column or a grouping key restores collection semantics.

Having

HAVING runs after grouping and may refer to grouping keys and aggregates:

rows := FROM Booking
    GROUP BY status
    HAVING sum(total) >= minimum
    SELECT { status, total: sum(total) }?

A guarded HAVING predicate if enabled uses the same fixed-statement parameter gate as guarded WHERE.

WHERE filters source rows before grouping. HAVING filters completed groups. Putting a row predicate in HAVING can change both portability and planning; use the clause matching the intended stage.

User aggregates

@sql.aggregate declarations can register compiler-known aggregate functions. Their signature and engine overloads are checked at compile time. This is not a runtime callback executed once per database row; it describes SQL supplied by the selected backend.

Keep the declared Atoll result type consistent with every engine overload. Runtime row decoding trusts the registered result shape after compilation.

Windowed aggregates are covered in Advanced.