---
title: Aggregates & Group By
description: >-
  Totals computed in SQLite with Firestore semantics - count, sum, average,
  minOf/maxOf, the groupBy extension, watches, and @Mirrored() for fields you
  never filter on.
---
> ## Documentation Index
> Fetch the complete documentation index at: https://docs.page/redfieldchristabel/butterfly/llms.txt
> Use this file to discover all available pages before exploring further.

Totals run in SQLite, not in Dart. `query.aggregate([...])` — Firestore's
aggregate API — computes `count`, `sum` and `average` (plus Cocoon's
`minOf`/`maxOf`) in one statement over the mirrored columns, with no
documents decoded.

The examples on this page use this model — `amountMinor` and `currency` are
mirrored because this app only ever totals or groups them, never filters or
sorts:

```dart
@Collection(
  'accounts/{accountId}/txns',
  indexes: [Index(['accountId', '-bookedOn'])],
)
class Txn {
  const Txn({
    required this.bookedOn,
    required this.amountMinor,
    required this.currency,
  });
  final int bookedOn; // yyyymmdd — filtered and sorted, so indexed
  @Mirrored()
  final int amountMinor; // summed and averaged, never filtered
  @Mirrored()
  final String currency; // a groupBy key, never filtered
}
```

## The aggregate call

```dart
final snapshot = await a1.collection(txns)
    .where(Txn$.bookedOn.isGreaterThanOrEqualTo(20260101))
    .aggregate([count(), sum(Txn$.amountMinor), average(Txn$.amountMinor)])
    .get();

print(snapshot.count); // documents matched (nulls count too)
print(snapshot.getSum(Txn$.amountMinor)); // int, nulls skipped, empty = 0
print(snapshot.getAverage(Txn$.amountMinor)); // double?, null when nothing to average
```

The semantics follow Firestore:

- `count` counts the documents the filters match — nulls count too.
- `sum` skips nulls; an empty sum is `0`. A sum of an `int` field is an
  `int` — exact, and summing past ±2^63 throws an
  `AggregateOverflowException` instead of wrapping.
- `average` is always a `double?` — `null` when no matching document has a
  value.
- Getters for functions you did not request throw.
- At most 5 functions per call, like Firestore.

The rules are the query rules: the same index requirement with the same
`MissingIndexException` (and `.allowFullScan()` to opt out), and the same
wait behind a running `bulkWriter()`. An `orderBy` + `limit` on the query
aggregate the first N documents in that order; without a `limit` the order
is irrelevant. A `startAfter` cursor is ignored — aggregates always run over
the whole filtered set.

`query.count()` is sugar for `aggregate([count()])` and behaves exactly like
before.

`minOf`/`maxOf` are Cocoon extensions (named so they don't clash with
`dart:math` — Firestore has no min/max aggregates): they compare in SQL
order and convert back to the field's type, and are `null` when no value
exists.

```dart
final bounds = await a1.collection(txns)
    .aggregate([minOf(Txn$.amountMinor), maxOf(Txn$.amountMinor)])
    .get();
print(bounds.getMin(Txn$.amountMinor)); // int? — the smallest debit
```

Only mirrored fields can be aggregated — an `Index([...])` member or a
`@Mirrored()` field; either copies the field into an `f_` column. Anything
else throws an `UnmirroredFieldException` naming both fixes: `@Mirrored()`
for totals and groups, an `Index([...])` when a query also filters or sorts
on the field.

## groupBy: a Cocoon extension (not in Firestore)

`query.groupBy([...]).aggregate([...])` buckets the results and aggregates
per group, in one statement:

```dart
final currencyKey = key(Txn$.currency);
final monthKey = bucket(Txn$.bookedOn, width: 100); // yyyymmdd -> yyyymm00
final groups = await a1.collection(txns)
    .where(Txn$.bookedOn.isGreaterThanOrEqualTo(20260101))
    .groupBy([currencyKey, monthKey])
    .aggregate([sum(Txn$.amountMinor), count()])
    .get();

for (final group in groups) {
  final month = group.key(monthKey) ~/ 100; // yyyymm
  print('${group.key(currencyKey)} $month: ${group.getSum(Txn$.amountMinor)}');
}
```

Four key kinds:

- `key(field)` — the field's own value, converted back to its type
  (`DateTime`, enum, ...). Nullable fields group their nulls into one
  group, sorted first.
- `paramKey(param)` — a path param, so a collection-group query can
  aggregate one group per parent:
  `db.collectionGroup(txns).query().allowFullScan().groupBy([paramKey(Txn$.accountId)])`
  (a collection group has no single index; with no filter or order to
  anchor one, the group query needs `.allowFullScan()`).
- `bucket(field, width: w)` — fixed-width bands `floor(v / w) * w`:
  `yyyymmdd` days into months (`width: 100`), prices into bands. Integer
  fields with integer widths give `int` keys (exact floor division, no
  floats, correct for negative amounts); double fields give `double` keys.
- `year(field, utcOffset:)`, `month(...)`, `day(...)` — calendar buckets on
  `DateTime` fields, **at a fixed `utcOffset`**. There are no IANA zones
  and no DST.

<Warning>
  **The `utcOffset` is required, and DST zones are not supported.** For a
  DST zone a fixed offset silently buckets the wrong wall-clock times around
  transitions. Run one aggregate per period with range filters on the
  instant instead — or store the local date as a `yyyymmdd` `int` and
  `bucket` it. Fixed-offset zones like UTC or `+08:00` are exact: a 23:30
  UTC txn lands in the next local day/month.
</Warning>

Rules for the whole call:

- Up to 4 keys; groups come ordered by the keys ascending, null keys first.
- `limit(n)` caps the number of groups.
- Each group carries the same aggregates as a whole-result snapshot —
  `count`, `getSum`, `getAverage` — plus `getMin`/`getMax`.
- `group.key(spec)` takes the same key spec object the query grouped by
  (`currencyKey` above — not the bare field) and converts the key back to
  the field's type; a spec the query did not group by throws.
- The filters follow the same index rule as `get()`.

## Watching aggregates

Both kinds have `watch()`, behaving like a query watch: the first result on
listen, re-computed when a commit touches the collection (a subcollection
scope only re-runs for its own paths), bursts coalesced, and an equal
consecutive result is never re-emitted.

```dart
final totalsSub = a1.collection(txns)
    .groupBy([bucket(Txn$.bookedOn, width: 100)])
    .aggregate([sum(Txn$.amountMinor)])
    .watch()
    .listen((groups) { /* rebuild the chart */ });
```

## Totals over fields you don't filter on: `@Mirrored()`

A field only gets a column when some `Index([...])` lists it — but a field
you only total or group by doesn't need an index. Annotate it `@Mirrored()`
and it gets the same `f_` column (same type mapping, nullability and CHECK
as an index field) with no index over it — exactly what the `Txn` model at
the top of this page does for `amountMinor` and `currency`.

Aggregates and `groupBy` read a mirrored column exactly like an indexed one.
A `where`/`orderBy` on it still follows the index rule —
`MissingIndexException` suggests the `Index([...])`, `.allowFullScan()`
opts out. A field that is both mirrored and indexed is just indexed: one
column, one index, no duplicate. `bulkWriter()` fills mirrored columns like
any other; there is no index to defer, so `deferIndexes` doesn't affect
them. Adding `@Mirrored()` to an existing collection adds and backfills the
column on the next open — one pass over the stored documents, in the open
transaction, crash-safe like the index backfill; removing the annotation
drops the column the same way.

### What mirroring costs (measured)

Measured on the rivals dataset at 1,000,000 transactions (Cocoon only; a
scratch copy of the schema with the amount index dropped, measured
2026-11-10 while an Android-emulator job was running on the same host —
byte counts are exact, the timings are ±):

| Schema variant | File size | Bytes per row |
|---|---|---|
| `Index(['amountMinor'])` (indexed) | 394,452,992 B | 394.5 |
| `@Mirrored()` only (column, no index) | 371,388,416 B | 371.4 |
| neither (no amount column) | 367,202,304 B | 367.2 |

So the amount column costs ~4.2 B/row and the index over it another
~23.1 B/row at 1M — mirroring instead of indexing a field you only total
saves ~6% of this table's bytes per row. Adding `@Mirrored()` to an existing
1M database backfills the column at the next open in ~12 s (one pass over
1M documents, in the open transaction; the second open skips it — 3 ms).
The in-place backfill leaves the file ~11% above its compacted form (403.1
vs 364.5 B/row — page fragmentation from the column add and the row
updates); `db.backupTo()` writes the compacted form (0.98 s at 1M), so a
backup or any `VACUUM` reclaims it.

When to pick `Index` instead: the moment any query filters or sorts on the
field, it needs the index anyway — and an index field is already mirrored
(one column, one index), so `@Mirrored()` on top of an `Index([...])` adds
nothing.

## The measured numbers

Aggregates push the math into SQLite — the wins start where row counts
dominate (the rivals dataset at 1,000,000 transactions; the box was busier
than usual that night, so treat every number as ±30%):

| Workload | before (paged, sums in Dart) | after (aggregates) |
|---|---|---|
| Monthly totals, one account, one year | 185 ms · 9,874 documents decoded | **35 ms · 12 group rows, zero decodes** |
| Year report, all 20 accounts | 1,430 ms | **376 ms** |

The year report is at parity with drift's own SQL `GROUP BY` (358 ms),
inside the noise band — the one workload a document API structurally could
not match until the pushdown landed. Counts were already `SELECT count(*)`
and measure unchanged. Full tables:
[AGGREGATES.md][cocoon-aggregates],
[RESULTS.md][cocoon-results].

[cocoon-aggregates]: https://github.com/redfieldchristabel/butterfly/blob/master/benchmarks/cocoon_rivals/AGGREGATES.md
[cocoon-results]: https://github.com/redfieldchristabel/butterfly/blob/master/benchmarks/cocoon_rivals/RESULTS.md
