# 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: ```jadpo 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? } ``` ```jadpo 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 } ``` ```jadpo use metering/types { ReplyKind } /// How many calls were replied one way. type ReplyTally = Output { reply: ReplyKind calls_made: Int } ``` ```jadpo 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? } ``` ```jadpo 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 { 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 } ``` ```jadpo 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 { from: ApiCall where: ApiCall.made_on >= from_day group: { reply: ApiCall.reply } aggregate: { calls_made: count() } order: [calls_made desc] limit: 3 } ``` ```jadpo 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) } } ``` ```jadpo 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 = [ 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 = 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 = 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](sql-forms.md), 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](sql-forms.md). 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](zones-and-local-times.md)). 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` 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](values-and-types.md)): 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` or `T`, never `T?` or `Page`, 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](field-restrictions.md), `policy.output_field_unproved`).