QuerySet aggregation — annotate() and aggregate()

The Databases & SQL domain's GROUP BY lesson, restated in Django's own vocabulary — aggregate() answers one number for the whole queryset, annotate() answers one number per row, and mixing them up produces the wrong shape of result.

Intermediate

3 min read

aggregate(): one number for the whole queryset

from django.db.models import Count, Sum, Avg
 
Order.objects.aggregate(total_revenue=Sum("amount"))
# {'total_revenue': 15420}
 
Order.objects.aggregate(order_count=Count("id"), avg_amount=Avg("amount"))
# {'order_count': 87, 'avg_amount': 177.24}

aggregate() collapses the entire queryset into a single dictionary of computed values — this is the direct equivalent of SELECT SUM(amount) FROM orders from the SQL domain's aggregation lesson, with no GROUP BY involved at all. Sum, Count, Avg, Max, Min are the same aggregate functions covered there, just expressed as Django classes imported from django.db.models instead of SQL function names. The keyword argument name (total_revenue=) becomes the key in the returned dict — it's not a magic name, just a label chosen for the result.

annotate(): one number per row, added to each object

from django.db.models import Count
 
customers = Customer.objects.annotate(order_count=Count("orders"))
 
for customer in customers:
    print(customer.name, customer.order_count)   # order_count is now an attribute on each object

annotate() is the fundamentally different operation: instead of collapsing to one result, it adds a computed value to each object in the queryset — every Customer in the result now has an order_count attribute available, computed per customer. This is the direct equivalent of SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id — Django generates that exact GROUP BY underneath, with Customer (not Order) as the thing being grouped, since annotate() is called on Customer.objects.

The rule that actually decides which one to use

This maps directly onto the SQL lesson's core distinction: aggregate() is "no GROUP BY, one row out" and annotate() is "GROUP BY the thing you called it on, one row per group out." Confusing them is a common real mistake — calling aggregate() when the actual goal was a per-customer breakdown returns one dict with no way to tell which customer contributed what; calling annotate() when a single overall total was wanted returns a queryset of many rows, each carrying its own per-row value, not the one number that was actually needed.

Filtering on an annotated value: this is where HAVING shows up

Customer.objects.annotate(order_count=Count("orders")).filter(order_count__gt=3)

This is exactly the SQL lesson's WHERE vs. HAVING distinction, restated in Django. .filter() called after .annotate(), referencing the annotated field (order_count), compiles to a HAVING clause — because it's filtering on the aggregated value, which only exists after grouping happened. A .filter() referencing an ordinary model field instead (Customer.objects.filter(active=True)) compiles to a plain WHERE, filtering rows before any grouping. Same method name, genuinely different generated SQL, depending entirely on what's being filtered.

Grouping by more than the default

from django.db.models.functions import TruncMonth
 
Order.objects.annotate(month=TruncMonth("created_at")).values("month").annotate(total=Sum("amount"))

annotate() groups by whatever the queryset was already going to return one row per — by default, that's each Customer (or Order, or whichever model). Explicitly calling .values("month") before the second annotate() changes what's being grouped by: this pattern groups orders by month instead of by individual order, producing "total revenue per month." This chained values() + annotate() pattern is genuinely one of the least obvious parts of Django's ORM at first — the position of .values() in the chain changes what GROUP BY column gets used, which is easy to get backwards without understanding the underlying SQL it's generating.

Why understanding the underlying SQL matters here specifically

# Looks similar, generates very different SQL:
Order.objects.aggregate(total=Sum("amount"))                              # 1 row, 1 number
Order.objects.values("customer").annotate(total=Sum("amount"))             # 1 row PER customer

aggregate() vs. annotate() is one of the places in Django's ORM where the Python method names alone don't make the actual SQL shape obvious — two lines that look almost identical produce a single summary row versus a full grouped result set. Having read the SQL domain's GROUP BY/HAVING lesson first is exactly what makes this Django-specific behavior predictable instead of something to memorize by trial and error.