beginnerintermediateadvanced

Django · Section 19

Aggregation and Annotation

Level: advanced
Read: 26 min
Concepts: 3

The load-bearing difference between aggregate() (one summary dict for the whole QuerySet) and annotate() (a per-object computed value on a still-chainable QuerySet), Count/Sum/Avg/Min/Max and their default= handling of empty results, conditional aggregation via filter=Q(...) alongside the distinct=True fix required when combining Count() across two or more relations in one call, and grouping via values()+annotate() — including why the ORDER of values()/filter()/annotate() changes what is actually computed, not just how many rows come back.

This section

Django Aggregation & Annotation / values() and values_list() functionsBugBytes

What is true here

aggregate() returns one summary dict for the whole QuerySet and is terminal; annotate() adds a computed value per object and stays chainable.
Sum/Avg/Min/Max return None for an empty QuerySet unless default= is given; Count always returns 0, with no default= parameter at all.
filter=Q(...) on an aggregate scopes just that one aggregate — useful for 2+ differently-scoped aggregates side by side; a single condition is simpler as a plain QuerySet.filter() first.
Combining Count() (or similar) across two or more different relations in one annotate() call needs distinct=True on each, or SQL JOINs silently inflate both counts.
values(field) placed BEFORE annotate() causes real grouping (one row per distinct value); placed after, it only selects output columns from an already-ungrouped result — and filter() before vs after annotate() changes whether it narrows the input or the aggregate's output.

What you will be able to do

Choose correctly between aggregate() and annotate() for a given reporting need
Handle empty-QuerySet aggregates correctly with default=
Combine multiple differently-scoped aggregates with filter=, and avoid join-inflated counts with distinct=True
Group results correctly with values()+annotate(), and reason about filter() placement relative to annotate()

annotate() vs aggregate()

One summary dict for the whole QuerySet, or a computed value attached to every object — and the aggregate functions themselves.

annotate() vs aggregate()

coreintermediate

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).

Think of it as

aggregate() answers "what's the one number for all of this" — average price across every book, a single dict back. annotate() answers "for EACH of these, what's its number" — every book keeps its own row, with one new computed column attached to it. The clue is right there in what each returns: aggregate() gives you a dict (one summary), annotate() gives you a QuerySet (still many rows, still chainable) — reaching for the wrong one is the difference between "the average book price" and "each book, annotated with something."

python

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

What we're doing: Get the average book price as a single number, and separately annotate every author with their own book count.

catalog/views.pypython

stats = Book.objects.aggregate(average_price=Avg("price"), total=Count("id"))
# {'average_price': 34.35, 'total': 214}

authors = Author.objects.annotate(book_count=Count("book"))
# each Author instance in this QuerySet now has .book_count

1: aggregate() ends the query right here, returning a dict — there is no further QuerySet to chain .filter() or .order_by() onto.
4: annotate() instead returns a QuerySet where every Author keeps its own row, each with a NEW book_count attribute computed for that specific author.

Why this works: A dashboard "average order value" widget needs aggregate() — one number. A customer list showing "3 orders" next to EACH customer needs annotate() — a per-row computed value. Confusing the two either throws away the per-object detail (using aggregate() when annotate() was needed) or asks for a per-object value where only a single summary was ever wanted.

Calling aggregate() when a per-object value was actually needed

Wrong

python

result = Author.objects.aggregate(book_count=Count("book"))
# {'book_count': 214} — a SINGLE total across ALL authors, not per-author

Better

python

authors = Author.objects.annotate(book_count=Count("book"))
# each author has their OWN book_count

What you see: A page meant to show "3 books" next to each author instead shows one grand total for the entire site, or crashes trying to iterate a dict as if it were a list of author objects.

Why: aggregate() always collapses the entire QuerySet into ONE summary dict, regardless of how many authors exist — it has no concept of "per author" at all. annotate() is the one that keeps every object as its own row while adding a computed value alongside it, which is what a per-author display actually needs.

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(), ...

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

Together

python

Book.objects.aggregate(Avg("price"))
# {'price__avg': 34.35} — ONE number

Book.objects.annotate(num_authors=Count("authors"))
# a QuerySet — EVERY book now has .num_authors

Remember: 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.

Conditional aggregation and distinct=True

Scoping one aggregate with filter=, and the join-inflation bug that appears once a second relation is combined into the same query.

Conditional aggregation and distinct=True

coreadvanced

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.

Think of it as

filter= on an aggregate is a WHERE clause scoped to just that one aggregate, not to the whole query — it lets Author.objects.annotate(...) compute "total books" and "highly-rated books" side by side in a single query, each counting a different subset, without two separate round trips. distinct=True solves an entirely different problem: when a query joins across TWO separate relations to compute two Counts at once, the join can multiply rows (every combination of the two related sets), so both counts come back too high unless each Count is told to count only DISTINCT related rows.

python

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

What we're doing: Show each author's total book count alongside their count of highly-rated books, in one query, without two separate round trips.

catalog/views.pypython

authors = Author.objects.annotate(
    total_books=Count("book"),
    highly_rated_books=Count("book", filter=Q(book__rating__gte=7)),
)
# author.total_books and author.highly_rated_books, both computed in one query

3: filter=Q(book__rating__gte=7) scopes THIS Count specifically — total_books still counts every book, unaffected by the filter on the other annotation.

Why this works: Using QuerySet.filter(book__rating__gte=7) instead would narrow the ENTIRE query to only highly-rated books, losing total_books entirely — filter= on the aggregate itself is what makes it possible to have two differently-scoped counts side by side in one annotate() call.

Combining two Count() annotations over different relations without distinct=True

Wrong

python

book = Book.objects.annotate(Count("authors"), Count("store")).first()
book.authors__count   # 6 — but the book actually has only 2 authors!

Better

python

book = Book.objects.annotate(
    Count("authors", distinct=True), Count("store", distinct=True),
).first()
book.authors__count   # 2 — correct

What you see: A Count() that was correct on its own (Count("authors") alone gives the right number) becomes inflated the moment a SECOND Count() over a different relation (Count("store")) is added to the same annotate() call — no error, just silently wrong numbers.

Why: Combining two relations in one query means the underlying SQL joins both — every combination of matching authors AND stores gets counted, multiplying rows (2 authors × 3 stores = 6 rows counted, not 2). distinct=True on each Count tells the database to count only distinct related rows, undoing that multiplication — this bug specifically requires TWO OR MORE relations combined in one call; a single Count() never has this problem.

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

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

Together

python

Author.objects.annotate(
    total_books=Count("book"),
    highly_rated_books=Count("book", filter=Q(book__rating__gte=7)),
)

# two DIFFERENT relations combined — needs distinct=True on both:
Book.objects.annotate(
    Count("authors", distinct=True),
    Count("store", distinct=True),
)

Remember: 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.

Grouping with values()

Turning annotate() into a real GROUP BY, and why the order of values()/filter()/annotate() changes what gets computed.

Grouping with values() + annotate()

standardadvanced

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.

Think of it as

annotate() by itself doesn't group anything — every model instance still gets its own row, just with an extra computed column. The moment values() appears BEFORE annotate(), the meaning flips: now the query groups by whatever values() named, and annotate() computes its aggregate PER GROUP instead of per object — two authors sharing the same name become one row with a combined average, not two separate rows. Where a filter() sits relative to annotate() matters for the same reason a WHERE vs a HAVING clause differ in raw SQL: filtering the base QuerySet first narrows what feeds INTO the aggregate; filtering after annotate() narrows the aggregate's OUTPUT instead.

python

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

What we're doing: Group orders by month and compute a per-month total revenue, rather than a per-order annotation.

orders/reports.pypython

monthly_revenue = (
    Order.objects.annotate(month=TruncMonth("placed_at"))
    .values("month")
    .annotate(total=Sum("total"))
    .order_by("month")
)
# one row per month: {"month": ..., "total": ...}

2: TruncMonth truncates each order's placed_at down to its month — computed per order at this point, no grouping yet.
3: values("month") is what actually triggers grouping — everything after this point (the second .annotate()) computes PER MONTH, not per order.

Why this works: Without values("month") before the second annotate(), Sum("total") would compute ONE grand total across every order in the entire QuerySet, since nothing would be telling Django to group by month — the values() call is what turns "one number for everything" into "one number per month."

Putting values() AFTER annotate() when grouping was the actual goal

Wrong

python

Order.objects.annotate(total=Sum("total")).values("customer_id", "total")
# total is the SAME grand-total number repeated on every row — no grouping happened

Better

python

Order.objects.values("customer_id").annotate(total=Sum("total"))
# one row per customer_id, each with THEIR OWN total

What you see: A report meant to show per-customer totals instead shows the exact same grand-total number repeated on every single row — technically not an error, just silently the wrong computation.

Why: values() has to come BEFORE annotate() to actually cause grouping — values() placed AFTER annotate() just selects which columns show up in the output of an already ungrouped (per-object) aggregate, it does not retroactively group anything. The order of these two calls is the entire difference between "grouped by customer" and "one number, repeated."

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"

Together

python

Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count("book"))
# num_books counts ONLY books rated > 3.0

Publisher.objects.annotate(num_books=Count("book", distinct=True)).filter(book__rating__gt=3.0)
# num_books counts ALL books — the filter just narrows WHICH publishers appear

Remember: 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.