Aggregations & Grouped Queries¶

Ferro aggregates with five methods on the columns themselves — count(), sum(), avg(), min(), max() — inside the same select() lambda that projects columns. There is no group_by() chainer anywhere in the API, and you will not miss it: the record shape is the grouping. Bare fields are the keys; aggregate fields are the measures.

The examples on this page use this schema:

AssignmentAnnotated

class Account(Model):
    id: int | None = Field(default=None, primary_key=True)
    label: str
    transactions: Relation[list["Transaction"]] = BackRef()


class Transaction(Model):
    id: int | None = Field(default=None, primary_key=True)
    amount: int
    price: Decimal | None = Field(default=None)
    memo: str
    account: Annotated[
        Account | None, ForeignKey(related_name="transactions")
    ] = None

class Account(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    label: str
    transactions: Relation[list["Transaction"]] = BackRef()


class Transaction(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    amount: int
    price: Annotated[Decimal | None, Field(default=None)]
    memo: str
    account: Annotated[
        Account | None, ForeignKey(related_name="transactions")
    ] = None

Global Aggregates¶

An aggregate is a method on a column reference: t.amount.sum(). Aggregate fields are user-named — they live in the dict selector form, where the key names the output field. A projection containing only aggregates collapses the whole result to exactly one record, read idiomatically with first():

        # An aggregate-only projection collapses to exactly one record:
        # the global aggregate, read idiomatically with first().
        row = await Transaction.select(
            lambda t: {
                "n": t.id.count(),
                "total": t.amount.sum(),
                "average": t.amount.avg(),
                "smallest": t.amount.min(),
                "largest": t.amount.max(),
            }
        ).first()

        assert row is not None
        assert row.model_dump() == {
            "n": 4,
            "total": 100,
            "average": 25.0,
            "smallest": 10,
            "largest": 40,
        }

Aggregates measure whatever where() leaves in — relation traversal included:

        # Aggregates measure whatever where() leaves in — traversal included.
        row = await (
            Transaction.select(lambda t: {"total": t.amount.sum()})
            .where(lambda t: t.account.label == "a1")
            .first()
        )
        assert row is not None and row.total == 30

The source column may itself traverse (t.account.balance.avg()): the traversal narrows to rows where the relation exists, exactly like a where() predicate on the same path, and shares that path's join with any other clause that references it.

What each aggregate returns¶

Result types are a pinned cross-backend contract, derived from the source column's Python type — the same query returns the same Python types on SQLite and Postgres, even where the databases themselves disagree (Postgres widens SUM(bigint) to numeric; SQLite averages everything as a float):

Aggregate	Source column	Result type
`count()`	any column	`int` — never `None`
`sum()`	`int` / `float` / `Decimal`	the source numeric type
`avg()`	`int` / `float`	`float`
`avg()`	`Decimal`	`Decimal` — never a silently lossy `float`
`min()` / `max()`	numeric, `str`, `datetime` / `date` / `time`	the source type, via the source codec

        # Result types are a pinned cross-backend contract derived from the
        # source column: avg over a Decimal column stays Decimal (never a
        # silently lossy float), on SQLite and Postgres alike.
        row = await (
            Transaction.select(lambda t: {"avg_price": t.price.avg()})
            .where(lambda t: t.price != None)  # noqa: E711
            .first()
        )
        assert row is not None
        assert isinstance(row.avg_price, Decimal)

Every aggregate result except count is T | None, because of what comes next.

Empty input: `None` and `0`, no COALESCE¶

SQL answers an aggregate over zero rows with NULL (COUNT with 0), and Ferro passes that through verbatim — "sum of no rows" and "sum of rows totaling zero" are different facts, and zero would be the wrong identity for min/max anyway:

        # Over zero matching rows, SQL's own empty-input semantics pass
        # through verbatim: one record, None for sum/avg/min/max, 0 for
        # count. No hidden COALESCE — "sum of no rows" and "sum of rows
        # totaling zero" stay distinguishable.
        row = await (
            Transaction.select(
                lambda t: {"n": t.id.count(), "total": t.amount.sum()}
            )
            .where(lambda t: t.amount > 10_000)
            .first()
        )
        assert row is not None
        assert row.n == 0
        assert row.total is None

`count()` counts non-NULL values¶

t.price.count() is SQL's COUNT(price): it counts rows where that column is non-NULL. Count rows regardless of any column with the primary key (t.id.count()):

        # COUNT(column) counts non-NULL values of that column — SQL
        # semantics, verbatim. Only one of four transactions has a price...
        row = await Transaction.select(
            lambda t: {"rows": t.id.count(), "priced": t.price.count()}
        ).first()
        assert row is not None
        assert row.rows == 4
        assert row.priced == 3

Grouped Queries: Bare Fields Are the Keys¶

Mix a plain field into an aggregate projection and the query becomes grouped — every non-aggregate field is a group key, and each group collapses to exactly one record:

        # Mix a plain field in and the query becomes GROUPED: every
        # non-aggregate field is a group key. There is no group_by() —
        # the record shape IS the grouping.
        rows = await (
            Transaction.select(
                lambda t: {"acct": t.account_id, "total": t.amount.sum()}
            )
            .where(lambda t: t.account_id != None)  # noqa: E711
            .order_by("acct")
            .all()
        )
        assert rows.model_dump() == [
            {"acct": 1, "total": 30},
            {"acct": 2, "total": 30},
        ]

This is SQL's own rule, made unwritable to violate: SQL already requires every bare selected column to be a group key, so declaring the grouping separately could only ever restate the projection or contradict it (Postgres rejects the contradiction at runtime; SQLite silently answers with an arbitrary row's value). Deriving GROUP BY from the record shape deletes that entire error class — the dict literal is the whole story.

Group keys may traverse, and traversal narrows exactly like a predicate:

        # Group keys may traverse. Traversal narrows (the account-less
        # transaction drops out) exactly like a where() predicate would.
        rows = await (
            Transaction.select(
                lambda t: {"account_label": t.account.label, "total": t.amount.sum()}
            )
            .order_by("account_label")
            .all()
        )
        assert rows.model_dump() == [
            {"account_label": "a1", "total": 30},
            {"account_label": "b1", "total": 30},
        ]

"Has no relation" is a visible bucket¶

Add left_join() and relation-less rows stay in — grouped under a None key instead of silently dropped:

        # left_join() keeps relation-less rows, so "has no account" becomes
        # a visible None-keyed group instead of a dropped row.
        rows = await (
            Transaction.select(
                lambda t: {"account_label": t.account.label, "total": t.amount.sum()}
            )
            .left_join(lambda t: t.account)
            .all()
        )
        buckets = {row.account_label: row.total for row in rows}
        assert buckets == {"a1": 30, "b1": 30, None: 40}

Zero rows, zero groups¶

A grouped query over zero matching rows returns zero records — unlike a global aggregate, there is no group to report on:

        # A grouped query over zero rows returns zero records — unlike a
        # global aggregate, there is no group to report on.
        rows = await (
            Transaction.select(
                lambda t: {"acct": t.account_id, "total": t.amount.sum()}
            )
            .where(lambda t: t.amount > 10_000)
            .all()
        )
        assert len(rows) == 0

Grouping is not partitioning¶

Grouping collapses rows: each group becomes one projected record of keys and measures, and the individual rows are gone. If you want complete model instances bucketed by a key — every transaction, arranged per account — that is a different, client-side operation over a full query:

from collections import defaultdict

by_account: dict[int | None, list[Transaction]] = defaultdict(list)
for txn in await Transaction.select().all():
    by_account[txn.account_id].append(txn)

Reach for aggregation when you want facts about groups; partition in Python when you want the rows themselves, organized.

Ordering Grouped Results¶

order_by on a projected query follows SQL's own ORDER BY scoping, pinned as three rules.

Strings resolve output field names first, then root columns. order_by("total") sorts by the aggregate you named total, even if the model happens to have a column with the same name:

        # The top-N idiom: keys + aggregates, order by the aggregate's
        # output name, limit the groups.
        top = await (
            Transaction.select(
                lambda t: {"account_label": t.account.label, "total": t.amount.sum()}
            )
            .order_by("total", "desc")
            .limit(1)
            .all()
        )
        assert len(top) == 1

That is the top-N idiom — keys plus aggregates, ordered by the measure's output name, limit() applied to the groups. limit()/offset() always act on groups in a grouped query, so pagination pages through the summary, not the underlying rows.

The lambda form spells source expressions — aggregates included. Where strings name outputs, lambdas write the expression itself:

        # order_by's lambda form spells the source expression — including
        # the aggregate itself.
        rows = await (
            Transaction.select(
                lambda t: {"acct": t.account_id, "total": t.amount.sum()}
            )
            .where(lambda t: t.account_id != None)  # noqa: E711
            .order_by(lambda t: t.amount.sum(), "desc")
            .order_by("acct")
            .all()
        )
        assert [row.acct for row in rows] == [1, 2]

An aggregate sort key must match a projected aggregate field (same function, same column, same path); an expression you did not project is a build-time error telling you to name it in the projection.

On an aggregate projection, every sort key must be a group key or an aggregate. Sorting groups by a column that is neither is the arbitrary-row trap again — SQLite would happily pick some row's value per group — so Ferro rejects it when you build the query, whether the key is a string, a lambda, or was chained before the select():

>>> Transaction.select(lambda t: {"acct": t.account_id, "total": t.amount.sum()}).order_by("memo")
ValueError: order_by('memo') on an aggregate projection must name a group key or an aggregate: ...

On a plain (non-aggregate) projection nothing changes: unselected root columns still sort, as they always have.

The Loud Limits¶

Everything below fails when you build the query — before any SQL, with an error that names the fix:

Source families with no portable meaning. sum()/avg() take numeric columns (int, float, Decimal); min()/max() take orderable ones (numeric, text, date/time); count() takes anything. Enum, UUID, JSON, and bool columns are rejected — min() over a UUID does not exist on Postgres, and max() over a native enum silently diverges between backends (definition order vs. lexical order). Where no portable meaning exists, build time is the only honest place to fail.
No aggregates in where(). WHERE filters rows before aggregation, so where(lambda t: t.amount.sum() > 100) raises pointing at having() — the post-aggregation filter, tracked in #291. Until it lands, filter rows with where() and compare aggregated results in Python.
The builtin-sum trap. sum(t.amount) (Python's builtin over a column reference) raises did you mean t.amount.sum()? instead of failing obscurely.
Aggregates are user-named. An aggregate outside the dict form (select(lambda t: t.amount.sum())) raises: give it an output name.
count()/exists() on an aggregate projection. "Count" is ambiguous between rows and groups, so both raise with both spellings: count matching rows with an unprojected query (Transaction.where(...).count()), count groups with len(await q.all()).

        # The loud limits, all at build time — before any SQL:

        # Aggregates over families with no portable cross-backend meaning.
        try:
            Transaction.select(lambda t: {"x": t.memo.sum()})
        except TypeError as exc:
            assert "string-typed" in str(exc)

        # Aggregates inside where() — filtering after aggregation is
        # having(), which is not built yet.
        try:
            Transaction.where(lambda t: t.amount.sum() > 100)
        except TypeError as exc:
            assert "having()" in str(exc)

        # The builtin-sum trap: aggregation is a method on the column.
        try:
            Transaction.select(lambda t: {"x": sum(t.amount)})  # type: ignore[arg-type]
        except TypeError as exc:
            assert "did you mean t.amount.sum()?" in str(exc)

        # Sorting a grouped query by a column that is not a group key would
        # let each group answer with an arbitrary row's value.
        try:
            Transaction.select(
                lambda t: {"acct": t.account_id, "total": t.amount.sum()}
            ).order_by("memo")
        except ValueError as exc:
            assert "group key or an aggregate" in str(exc)

        # count()/exists() on an aggregate projection are ambiguous between
        # rows and groups; both spellings are in the error.
        try:
            Transaction.select(
                lambda t: {"acct": t.account_id, "total": t.amount.sum()}
            ).count()
        except ValueError as exc:
            assert "len(await q.all())" in str(exc)