Annotations & Aggregations
Compute per-row values with annotate() and whole-table rollups with aggregate() — all in SQL.
These two are constantly confused, so let's nail the distinction first:
aggregate()collapses the whole queryset into a single dictionary of numbers. It ends the chain — you get a value, not a queryset.annotate()attaches a computed value to each row and returns a queryset you can keep filtering and ordering.
from django.db.models import Count, Avg
# aggregate: one number for the whole table
Book.objects.aggregate(avg_price=Avg('price'))
# -> {'avg_price': Decimal('24.50')}
# annotate: a number attached to every author row
Author.objects.annotate(num_books=Count('books')).order_by('-num_books')The multi-join counting trap
When you annotate across two different relations at once — say Count('books') and Count('articles') on the same author — the JOINs multiply and your counts come out inflated. The fix is distinct=True inside the aggregate, or splitting into subquery-based annotations.
Case/When let you build conditional aggregates — the SQL equivalent of a pivot table — counting how many orders fall into each status in a single query rather than N queries.
Example
from django.db.models import (
Count, Sum, Avg, Q, F, Case, When, Value, DecimalField, IntegerField
)
from django.db.models.functions import Coalesce, TruncMonth
# 1) Conditional aggregation (a pivot in one query):
# per customer, how many orders are paid vs pending, and lifetime value.
customers = Customer.objects.annotate(
paid_orders=Count('orders', filter=Q(orders__status='paid')),
pending_orders=Count('orders', filter=Q(orders__status='pending')),
lifetime_value=Coalesce(
Sum('orders__total', filter=Q(orders__status='paid')),
Value(0),
output_field=DecimalField(max_digits=12, decimal_places=2),
),
).filter(paid_orders__gt=0).order_by('-lifetime_value')
# 2) Case/When to bucket rows, then aggregate the buckets:
tiers = Customer.objects.aggregate(
whales=Count('id', filter=Q(lifetime_value__gte=1000)),
regulars=Count('id', filter=Q(lifetime_value__lt=1000, lifetime_value__gte=100)),
casual=Count('id', filter=Q(lifetime_value__lt=100)),
)
# 3) Time-series rollup: revenue per month.
monthly = (Order.objects
.filter(status='paid')
.annotate(month=TruncMonth('created'))
.values('month')
.annotate(revenue=Sum('total'), orders=Count('id'))
.order_by('month'))
# 4) Guarding against fan-out double counting on two relations:
authors = Author.objects.annotate(
books=Count('books', distinct=True),
talks=Count('talks', distinct=True),
)When to use it
- A developer uses annotate(comment_count=Count('comments')) to display comment counts on a blog index page.
- A team uses aggregate(Avg('rating')) to compute the average product rating for a reporting dashboard.
- A developer groups orders by date with annotate and values to build a daily revenue chart.
More examples
Annotate with Count
Adds a comment_count column to each Post in the queryset and orders by it, all in one query.
from django.db.models import Count
posts = Post.objects.annotate(
comment_count=Count("comments")
).order_by("-comment_count")Aggregate across the queryset
Computes aggregate statistics across all products and returns them as a single dictionary.
from django.db.models import Avg, Max, Min, Sum
stats = Product.objects.aggregate(
avg_price=Avg("price"),
max_price=Max("price"),
min_price=Min("price"),
total_stock=Sum("stock"),
)Group by with values() + annotate()
Groups orders by day and sums revenue per day using TruncDate and annotate for a time-series report.
from django.db.models import Sum
from django.db.models.functions import TruncDate
daily_revenue = (
Order.objects
.annotate(date=TruncDate("created_at"))
.values("date")
.annotate(revenue=Sum("total"))
.order_by("date")
)
Discussion