Annotations & Aggregations

Compute per-row values with annotate() and whole-queryset summaries with aggregate() — letting the database do the counting, summing and grouping.

The difference between annotate() and aggregate() trips people up, so pin it down once:

aggregate() collapses the whole queryset into a single dictionary of results. "What is the average price across all products?"
annotate() attaches a computed value to each row. "How many comments does each article have?" It is Django's GROUP BY.

Annotate is GROUP BY

When you annotate across a relation, Django groups by the model's primary key. Author.objects.annotate(n=Count("books")) gives every author an extra .n attribute holding their book count — one query, computed in SQL, no N+1.

Two traps that produce wrong numbers

Double-counting across multiple joins. Annotating two different Count()s over two relations multiplies rows together. Pass distinct=True to each Count, or split into separate queries.
filter() ordering matters. A filter() before annotate() limits which rows are counted; a filter() after filters on the annotation itself (the SQL HAVING clause). Order your chain deliberately.

Reach for Case/When for conditional aggregates, and Coalesce to turn a NULL sum into 0.

Example

from django.db.models import Count, Sum, Avg, Q, Case, When, DecimalField, Value
from django.db.models.functions import Coalesce
from .models import Author, Order

# --- aggregate(): one summary dict for the whole queryset ---
stats = Order.objects.aggregate(
    total=Coalesce(Sum("amount"), Value(0), output_field=DecimalField()),
    avg=Avg("amount"),
    count=Count("id"),
)
# -> {"total": Decimal("12840.00"), "avg": Decimal("42.80"), "count": 300}

# --- annotate(): a computed value PER ROW (this is GROUP BY) ---
authors = (
    Author.objects
    .annotate(
        published_books=Count("books", filter=Q(books__status="published"), distinct=True),
        total_sales=Coalesce(Sum("books__sales"), 0),
    )
    .filter(published_books__gte=1)     # HAVING: filter ON the annotation
    .order_by("-total_sales")
)
for a in authors:
    print(a.name, a.published_books, a.total_sales)   # no N+1: all in one query

# --- Conditional aggregate with Case/When ---
Order.objects.aggregate(
    big_orders=Count(Case(When(amount__gte=100, then=1))),
)

When to use it

A developer annotates a product queryset with avg_rating from related reviews to sort by popularity in one query.
A team annotates an order list with the total item count so the template renders it without an extra query per row.
A developer uses conditional annotations with Case/When to label records as 'hot', 'warm', or 'cold' based on age.

More examples

Annotate with aggregate

Adds avg_rating and review_count columns to each product row in a single query, then sorts by rating.

from django.db.models import Avg, Count

products = Product.objects.annotate(
    avg_rating=Avg("reviews__rating"),
    review_count=Count("reviews"),
).order_by("-avg_rating")

Conditional annotation with Case/When

Uses Case/When to add a 'freshness' label per post based on its age, computed in the database.

from django.db.models import Case, When, Value, CharField
from django.utils import timezone
from datetime import timedelta

now = timezone.now()
posts = Post.objects.annotate(
    freshness=Case(
        When(published_at__gte=now - timedelta(days=1), then=Value("hot")),
        When(published_at__gte=now - timedelta(days=7), then=Value("warm")),
        default=Value("old"),
        output_field=CharField(),
    )
)

Annotate for subquery

Annotates each post with its latest comment body using a correlated subquery expression.

from django.db.models import OuterRef, Subquery

latest_comment = Comment.objects.filter(
    post=OuterRef("pk")
).order_by("-created").values("body")[:1]

posts = Post.objects.annotate(latest_comment=Subquery(latest_comment))

Discussion

Be the first to comment on this lesson.