Counts, sums and groups

A query can count and add up the records it finds instead of returning them. aggregate: names each total the database works out, and group: splits the records into groups, giving one row per group. This API service reports its calls by month, counts them by how they were replied to, and totals an account's calls:

use metering/types { CallCharge, CallDay, CallWait, ReplyKind, SpentTokens }/// One call an account made to the API.entity ApiCall {    id: Uuid    made_on: CallDay    tokens_spent: SpentTokens    charge: CallCharge    reply: ReplyKind    wait: CallWait?}

use metering/entities { ApiCall }/// A month of calls on an invoice.type MonthOfCalls = Output {    call_year: Int    call_month: Int    calls_made: Int    tokens_billed: Int    amount_billed: Money    largest_call: ApiCall.tokens_spent    mean_tokens: Decimal}

use metering/types { ReplyKind }/// How many calls were replied one way.type ReplyTally = Output {    reply: ReplyKind    calls_made: Int}

use metering/entities { ApiCall }/// An account's calls since a day, all together.type CallSummary = Output {    calls_made: Int    amount_billed: Money    slowest_wait: ApiCall.wait    mean_tokens: Decimal?}

use metering/entities { ApiCall }use metering/types { CallDay, MonthOfCalls }/// Calls made since `from_day`, by month, the latest month first.query monthly_calls(from_day: CallDay) -> List<MonthOfCalls> {    from: ApiCall    where: ApiCall.made_on >= from_day    group: {        call_year: temporal.year(ApiCall.made_on)        call_month: temporal.month(ApiCall.made_on)    }    aggregate: {        calls_made: count()        tokens_billed: sum(ApiCall.tokens_spent)        amount_billed: sum(ApiCall.charge)        largest_call: max(ApiCall.tokens_spent)        mean_tokens: average(ApiCall.tokens_spent, scale: 2, rounding: Rounding.half_even)    }    order: [call_year desc, call_month desc]    limit: 24}

use metering/entities { ApiCall }use metering/types { CallDay, ReplyTally }/// How many calls since `from_day` were answered each way, the most first.query reply_tallies(from_day: CallDay) -> List<ReplyTally> {    from: ApiCall    where: ApiCall.made_on >= from_day    group: {        reply: ApiCall.reply    }    aggregate: {        calls_made: count()    }    order: [calls_made desc]    limit: 3}

use metering/entities { ApiCall }use metering/types { CallDay, CallSummary }/// Every call since `from_day`, all together.query totals_since(from_day: CallDay) -> CallSummary {    from: ApiCall    where: ApiCall.made_on >= from_day    aggregate: {        calls_made: count()        amount_billed: sum(ApiCall.charge)        slowest_wait: max(ApiCall.wait)        mean_tokens: average(ApiCall.tokens_spent, scale: 2, rounding: Rounding.half_even)    }}

use metering/entities { ApiCall }use metering/types { CallCharge, CallSummary, MonthOfCalls, ReplyKind, ReplyTally }use metering/queries { monthly_calls, reply_tallies, totals_since }test "calls add up by month, and each reply is counted" {    var one_penny: CallCharge = CallCharge(money.from_minor(1, Currency.gbp))    var calls_recorded: List<ApiCall> = [        record_call("2026-08-31", 120, one_penny, ReplyKind.replied, 2s),        record_call("2026-09-01", 100, one_penny, ReplyKind.replied, none),        record_call("2026-09-30", 41, one_penny, ReplyKind.throttled, 5s),        record_call("2026-09-30", 0, one_penny, ReplyKind.replied, none)    ]    assert list.count(calls_recorded) == 4    var by_month: List<MonthOfCalls> = monthly_calls("2026-08-01")    assert list.map(by_month, month -> month.call_month) == [9, 8]    assert list.map(by_month, month -> month.calls_made) == [3, 1]    assert list.map(by_month, month -> month.tokens_billed) == [141, 120]    assert list.map(by_month, month -> money.minor(month.amount_billed)) == [3, 1]    assert list.map(by_month, month -> month.largest_call) == [100, 120]    assert list.map(by_month, month -> month.mean_tokens) == [47.00, 120.00]    var tallies: List<ReplyTally> = reply_tallies("2026-08-01")    assert list.map(tallies, tally -> tally.reply == ReplyKind.replied) == [true, false]    assert list.map(tallies, tally -> tally.calls_made) == [3, 1]    var since_september: CallSummary = totals_since("2026-09-01")    assert since_september.calls_made == 3    assert since_september.slowest_wait == 5s    assert since_september.mean_tokens == 47.00}test "a summary of no calls counts nothing" {    var nothing: CallSummary = totals_since("2026-01-01")    assert nothing.calls_made == 0    assert nothing.amount_billed == money.from_minor(0, Currency.gbp)    assert nothing.slowest_wait == none    assert nothing.mean_tokens == none}

monthly_calls gives one row for each month that has calls. reply_tallies gives one for each reply that some call got, so refused appears only once a call is refused.

Both blocks are shaped like include:: each entry is named after the result field it fills. Each row is a value of an Output (or Object) type. Every field of that type is a group or an aggregate of the same name, and every group and aggregate is a field of it. Nothing else of the entity is read, since the records in a group have no one value of any other field. count, sum, min, max and average are words of aggregate: only, so they take no namespace and mean nothing elsewhere.

jadpo sql monthly_calls prints the one statement it sends, on two levels. A grouped subquery works out the groups and totals: its SELECT of each group and aggregate, the conditions, and GROUP BY the groups. The outer statement selects, orders and limits them, so an average names its sum and its count once. A Money sum's currency is the one its type fixes, sent as a parameter.

A query otherwise computes nothing, and aggregates are the exception. A time bucket and an average are written with their standard operations' SQL forms, and the rest are PostgreSQL's count, sum, min and max. So an aggregate query is always one statement, however many records it counts.

The aggregates

• count() is how many records there are, an Int.
• sum(F) adds up a field built on Int, Decimal or Duration, a quantity, or Money whose type fixes currency:. Two currencies can't be added, and a sum of nothing needs a currency for its zero. A percentage isn't summed. A sum gives what + gives: the field's representation (Int, Decimal, Duration, the quantity or Money), so a money or quantity sum keeps its kind.
• min(F) and max(F) give the smallest and largest value of a field, as the field's own type. The field is one a query can sort by (numbers, times, dates, durations, quantities, percentages, and text by code point), other than an identity, since PostgreSQL has no min of a uuid.
• average(F, scale:, rounding:) of a field built on Int, or on Decimal with at most scale: places, is a Decimal with exactly scale: places: the exact mean, rounded once as rounding: says. Both are written out, a whole number from 0 to 38 and a Rounding, as decimal.round's are, since an average rarely comes out exact and the rounding is part of what the report says. An average of durations, quantities or money isn't offered until an application needs one.

Each aggregate gives exactly what code working from the records gives:

• count() is list.count.
• sum is list.sum, + for durations and quantities, or money.sum with the type's currency.
• min and max are the values list.min_by and list.max_by find.
• average is decimal.divide of the sum by the count, at scale: places, with rounding:. It goes through decimal.divide's SQL form, which works the quotient out exactly as the runtime does, where PostgreSQL's own avg and / round to places they choose.

A whole number, a duration and money's minor units are summed exactly and cast back to bigint, so a sum past 64 bits stops the read. A decimal past 38 digits fails as it is read. Both are defects in code.

sum, min, max and average skip a field's none values, as a list of the values that are there would be summed. count() counts records, whatever their fields hold; to count a field's values, add a condition (where: ApiCall.wait != none). A group whose values are all none sums to zero, and its min, max and average are none.

The groups

A group is a field, or a time bucket: temporal.year, temporal.month or temporal.day of a field built on CalendarDate, each an Int through its SQL form. The field itself is the day, and a year and a month together are a month.

A group field is built on Bool, Int, Text, Uuid (a reference among them), CalendarDate or a plain enum, types whose stored values are equal exactly when Jadpo's are. A field that may be none puts its records without a value in a group of their own, none.

These can't be groups:

• Decimals, quantities and percentages, since numeric equality puts 1.0 and 1.00 in one group, and only one of them could be shown.
• Instants, durations, money and structured values. An instant has no bucket either: its day needs a zone, and PostgreSQL's time zone database can differ from the one built into every Jadpo build (Zones and local times). So no SQL form of temporal.in_zone gives exactly what code gives. Until that is decided, a report keeps the day as a CalendarDate field.

The result

Each group and aggregate fills the result's field of its name. That field takes its type, or one it widens to, as a stored value fills a field (calls_made: Int, largest_call: ApiCall.tokens_spent). A field that can be none needs a T?: a group of a field that may be none, and min, max and average of such a field or with no group:.

With group:, the query returns List<T> with order: and limit:, one row per group that has records. So no group is empty: a month without calls gives no row, as SQL's GROUP BY gives none, and filling the gaps is up to the caller.

order: names groups and aggregates by their own names, never the entity's fields. The compiler adds each group order: doesn't name as a tie-break, ascending, since the groups tell the rows apart. A group sorts in Jadpo's own order (Values and types): numbers by value, text by code point (COLLATE "C"), days in time order, false before true, and an enum's variants in their declared order (array_position of the codes it is stored as). An aggregate sorts when its type is ordered, which a Money sum isn't. A total that may be none sorts where PostgreSQL puts NULL, as a list query's order field that may be none does.

Without group:, the query returns T, one row over every record it finds, and takes no order:, limit: or missing:, since there is always the row. When nothing matches, count() is 0, a sum is zero (in the type's currency for Money, at its scale for a Decimal), and min, max and average are none.

What an aggregate query doesn't take

An aggregate query returns List<T> or T, never T? or Page<T>, and reads one entity. It takes no include:, no page:, no missing: and no nearest order, since a page of groups would need a cursor over groups and nothing has asked for one.

Its conditions are a query's usual where: and where_present: lines. Policy and the lifecycle's visible: limit the records it counts, as they limit any query's. Every field a group or an aggregate reads must be one every caller may read, as an output's fields must be (Field restrictions, policy.output_field_unproved).