aggregate() and annotate() both push calculations into the database, but they answer different questions.
The difference
| aggregate() | annotate() | |
|---|---|---|
| Returns | A dictionary, one row | A queryset, one row per object |
| Chainable | No | Yes |
| Typical use | Totals, dashboards | Per-row values, filter/order by computed data |
from django.db.models import Count, Sum, Avg # aggregate: one summary Order.objects.filter(status="paid").aggregate( revenue=Sum("total"), average=Avg("total"), orders=Count("id") ) # annotate: one value per customer Customer.objects.annotate(order_count=Count("orders"), total_spent=Sum("orders__total")) Replace loops and N+1
# Slow total = sum(o.total for o in Order.objects.filter(status="paid")) # Fast total = Order.objects.filter(status="paid").aggregate(t=Sum("total"))["t"] Trap 1: join inflation (silent wrong numbers)
Customer.objects.annotate( order_count=Count("orders"), item_count=Count("orders__items"), ) Two aggregates across different multi-valued relations multiply rows. Fixes:
# distinct=True for counts Customer.objects.annotate( order_count=Count("orders", distinct=True), item_count=Count("orders__items"), ) Or use a Subquery for one of them:
from django.db.models import Subquery, OuterRef, Count, IntegerField from django.db.models.functions import Coalesce item_counts = ( OrderItem.objects .filter(order__customer=OuterRef("pk")) .order_by() .values("order__customer") .annotate(c=Count("id")) .values("c") ) customers = Customer.objects.annotate( order_count=Count("orders"), item_count=Coalesce(Subquery(item_counts, output_field=IntegerField()), 0), ) Don't use distinct=True with Sum or Avg: it de-duplicates values, not rows.
Trap 2: one query per number
from django.db.models import Q Order.objects.aggregate( paid=Count("id", filter=Q(status="paid")), refunded=Count("id", filter=Q(status="refunded")), revenue=Sum("total", filter=Q(status="paid")), ) One scan, three answers.
Trap 3: group by with default ordering
Order.objects.order_by().values("status").annotate(n=Count("id")) values() before annotate() groups by those fields. A default Meta.ordering leaks into GROUP BY unless you clear it.
Trap 4: annotating just to filter
Customer.objects.alias(order_count=Count("orders")).filter(order_count__gt=10) alias() (Django 3.2+) computes the value without selecting it. Also filter on plain columns before annotating so fewer rows get grouped.
Trap 5: counts used as existence checks
from django.db.models import Exists, OuterRef Customer.objects.filter(Exists(Order.objects.filter(customer=OuterRef("pk")))) Big tables
- Index filter columns and check
qs.explain(analyze=True) - Cache totals that can be slightly stale
- Use summary tables or materialized views for heavy reports
- Count without annotations when you can, since
.count()on an annotated queryset may wrap it in a subquery
Full guide
The complete post covers aggregating over annotations, a decision guide, a checklist, and an FAQ:
👉 https://blog.reiteachaigean.com/blog/django-orm-annotate-vs-aggregate-performance
Related reads:
- select_related vs prefetch_related: Practical Examples
- Django ORM Optimization for Millions of Records
- How to Fix Slow Django Admin Pages
What's the nastiest ORM aggregation bug you've hit?