Aggregation and Annotation

annotate() vs aggregate(), conditional counts, distinct=True, and grouping with values()

aggregate() = one dict; annotate() = per-objectthe whole difference is what each returns
Two Count()s over different relations?add distinct=True or both counts inflate silently
filter= scopes ONE aggregatereach for it only with 2+ differently-scoped aggregates together
values() BEFORE annotate() groupsthe reverse order just selects columns, no grouping happens

1.annotate() vs aggregate()

aggregate() computes ONE summary value across an entire QuerySet and returns a plain dict — it is a terminal operation, not chainable further. annotate() computes a value PER OBJECT and returns a QuerySet, so every book gets its own num_authors, and that QuerySet stays fully chainable (filter(), order_by(), another annotate()). Count/Sum/Avg/Min/Max all support a default= to control what an EMPTY result returns (Count never accepts default — it always returns 0 for none).

Key features

aggregate() returns a plain dict: {"price__avg": 34.35} by default, or a custom key via aggregate(average_price=Avg("price"))
annotate() returns a QuerySet — each object gets the computed value as a real attribute (book.num_authors), and the QuerySet stays chainable
Sum/Avg/Min/Max (not Count) accept default=... — an empty QuerySet's Sum returns None without it, or the given default with it
Count() has no default= parameter — it always returns 0 for an empty result, never None
Multiple aggregate functions can be combined in one aggregate() or annotate() call: aggregate(Avg("price"), Max("price"), Min("price"))

annotate() vs aggregate()

annotate() vs aggregate()
Aspect	annotate()	aggregate()
Computed per	each object	the whole QuerySet, once
Returns	a QuerySet (still chainable)	a plain dict
Typical use	book.num_authors on every book	the average price across all books

One summary vs. one value per object

aggregate()

Computes ONE value across the whole QuerySet
Returns a plain dict — {"price__avg": 34.35}
Terminal — not chainable further

annotate()

Computes a value for EACH object
Returns a QuerySet — every row keeps .book_count
Still chainable — filter(), order_by(), ...

Command

python

Model.objects.aggregate(average=Avg("field"))                # one dict
Model.objects.annotate(computed=Count("related"))             # a QuerySet, per-object

aggregate() → one summary dict for the whole QuerySet (terminal, not chainable). annotate() → a per-object computed value on a QuerySet (still chainable). Sum/Avg/Min/Max return None on an empty QuerySet unless default= is given; Count always returns 0.

2.Conditional aggregation and distinct=True

filter=Q(...) on an aggregate function (Count("book", filter=Q(book__rating__gte=7))) computes that aggregate over only a SUBSET of rows — useful when a single annotate() call needs two different conditional counts side by side. distinct=True on an aggregate fixes a specific, easy-to-miss bug: combining two Count() annotations across two different relations in one query can multiply rows via SQL JOINs, silently inflating both counts.

Key features

filter=Q(...) inside Count()/Sum()/etc. computes the aggregate over rows matching that condition specifically — different from QuerySet.filter(), which narrows the WHOLE query
Use filter= specifically when 2+ conditional aggregates are needed side by side on the SAME relation; a single conditional aggregate is usually simpler as a plain QuerySet.filter() first
Combining Count() over two DIFFERENT relations in one annotate() call can multiply rows via the underlying SQL JOINs, inflating both counts
distinct=True on each affected Count() (Count("authors", distinct=True)) fixes this by counting only distinct related rows per object
This join-inflation bug does not show up with a single Count() annotation — it specifically appears once a second, unrelated Count() (or similar aggregate) is added to the same annotate() call

filter= vs distinct= on an aggregate

filter= vs distinct= on an aggregate
Tool	Problem it solves
filter=Q(...)	compute an aggregate over only a subset of related rows, alongside an unfiltered one
distinct=True	fix row-multiplication when combining Count() across two different relations in one query

Two Counts over different relations, without distinct=True

Book.objects.annotate(...)

Count("authors"), Count("store") in one call

SQL joins both relations

every author × every store combination

Rows multiply

2 authors × 3 stores = 6 rows counted, not 2

Fix: distinct=True

Count("authors", distinct=True) counts only distinct related rows

Command

python

Count("relation", filter=Q(condition))   # a conditional aggregate
Count("relation", distinct=True)          # fixes join-multiplied counts

filter=Q(...) on an aggregate scopes that ONE aggregate — reach for it only when 2+ differently-scoped aggregates are needed together; a single condition is simpler as QuerySet.filter() first. distinct=True is required whenever combining Count() (or similar) across two or more DIFFERENT relations in one annotate() call, to undo join-caused row multiplication.

3.Grouping with values() + annotate()

annotate() alone computes a value per OBJECT (one row per model instance). values("field").annotate(...) instead groups by whatever fields are named in values() first — the equivalent of SQL's GROUP BY — so the result is one row per DISTINCT value of that field, not one row per object. The ORDER of values()/filter()/annotate() changes the generated SQL: a filter() BEFORE annotate() narrows what gets aggregated; a filter() AFTER annotate() filters on the already-computed aggregate instead.

Key features

annotate() alone: one row per model instance, each with a computed value attached — no grouping happens
values(field).annotate(...): one row per DISTINCT value of field — a real GROUP BY, computed per group
filter() BEFORE annotate() narrows which rows feed into the aggregate calculation (closer to SQL's WHERE)
filter() AFTER annotate() filters on the aggregate's OWN computed result (closer to SQL's HAVING)
These two orderings can produce genuinely different numbers, not just different row counts — the same query written either way answers a subtly different question

Where filter() sits relative to annotate() changes the answer

Where filter() sits relative to annotate() changes the answer
Order	Meaning	Example question answered
filter().annotate()	aggregate computed only over the FILTERED rows	"for books rated > 3, how many per publisher?"
annotate().filter()	aggregate computed over ALL rows, then filtered	"publishers with at least one book rated > 3, showing their TOTAL book count"

Command

python

Model.objects.values("group_field").annotate(total=Count("id"))   # GROUP BY group_field

annotate() alone: one row per object, no grouping. values(field) BEFORE annotate(): real GROUP BY, one row per distinct value of field. filter() before annotate() narrows what feeds the aggregate (WHERE-like); filter() after annotate() filters the aggregate's result (HAVING-like) — these are genuinely different computations, not just different row counts.

Spotted a mistake on this page? Tell us.

Read Sheet