In Django's ORM, what is the difference between aggregate() and annotate(), and what does each one return?
answer
- one summary versus one value per row
- a dict versus a QuerySet
- terminal versus chainable
- default alias field__function
basics
~10 saggregate() computes summary values over the whole QuerySet and immediately returns a dict. annotate() adds a computed value to every object and returns a new, lazy QuerySet you can keep filtering and ordering.
solid answer
~30 s`aggregate()` is terminal: `Order.objects.aggregate(total=Sum("amount"))` runs the query at once and returns a dict such as `{"total": Decimal("1830.00")}`; without a name the key is the default alias, `amount__sum`. `annotate()` is per object: `Region.objects.annotate(n_customers=Count("customers"))` returns a QuerySet in which each `Region` carries an `n_customers` attribute, grouped by the model's primary key, and you can still `filter()`, `order_by()` or even `aggregate()` over that annotation. Both accept the same aggregate classes (`Count`, `Sum`, `Avg`, `Min`, `Max`...). On an empty set `Sum` and friends return `None` unless you pass `default=`, while `Count` returns `0`.
code
python · 14 linesfrom django.db.models import Count, Sum
# Terminal: returns a dict right away.
totals = Order.objects.filter(status="paid").aggregate(
revenue=Sum("amount", default=0), orders=Count("id")
)
# {'revenue': Decimal('1830.00'), 'orders': 6}
# Lazy: a QuerySet of Region objects, each with n_customers.
busy = (
Region.objects.annotate(n_customers=Count("customers"))
.filter(n_customers__gte=10)
.order_by("-n_customers")
)go deeper
Recall the shapes: aggregate() returns a dict with one summary, annotate() returns a QuerySet with a value on every object.
Explain laziness and SQL: aggregate() executes at once, annotate() adds GROUP BY on the primary key, and filters on annotations become HAVING.
Spot the Python loop of per-row count() calls that one annotate() replaces, and guard reports against None from empty aggregates with default=.
Decide which reporting numbers belong in live ORM annotations and which in precomputed tables, based on data volume and how fresh the numbers must be.
## Two methods, two shapes of result Django's ORM offers two ways to ask the database for computed values: - **`aggregate(*args, **kwargs)`** computes one or more summary values over **the whole QuerySet** and returns a **plain Python dict**. It is **terminal**: calling it executes the SQL immediately, just like `count()` or `list()`. - **`annotate(*args, **kwargs)`** adds a computed value **to every object** in the QuerySet and returns a **new QuerySet**. It is **lazy** and **chainable**: nothing runs until you iterate, and you can keep adding `filter()`, `order_by()` or `values()`. | | `aggregate()` | `annotate()` | |---|---|---| | Returns | `dict` | `QuerySet` | | Rows in the result | one summary | one per object (or per group with `values()`) | | Executes | immediately | when evaluated | | SQL shape | `SELECT SUM(...) FROM ...` | `SELECT ..., SUM(...) ... GROUP BY <pk>` | | Can be chained | no | yes | Both take the same **aggregate expressions** from `django.db.models`, such as `Count`, `Sum`, `Avg`, `Min`, `Max`, `StdDev`, `Variance` and, since Django 6.0, `AnyValue`. ## `aggregate()` in practice ```python from django.db.models import Avg, Sum Order.objects.filter(status="paid").aggregate(Sum("amount")) # {'amount__sum': Decimal('1830.00')} Order.objects.filter(status="paid").aggregate(total=Sum("amount"), avg=Avg("amount")) # {'total': Decimal('1830.00'), 'avg': Decimal('305.00')} ``` Key points: - Without a keyword, the dict key is the **default alias** `<field>__<function>`, e.g. `amount__sum`. - A complex expression such as `Sum(F("amount") * 2)` has no default alias, so you must name it; otherwise Django raises `TypeError`. - Because the result is a dict, you cannot chain `.filter()` after it. ## `annotate()` in practice ```python from django.db.models import Count regions = Region.objects.annotate(n_customers=Count("customers")) for region in regions: print(region.name, region.n_customers) ``` Here Django joins `Customer`, groups by the `Region` primary key and exposes `n_customers` on each instance. The annotation behaves like a read-only field on those objects: - **filter on it**: `.filter(n_customers__gte=10)` becomes a `HAVING` clause; - **order by it**: `.order_by("-n_customers")`; - **aggregate over it**: `.aggregate(Avg("n_customers"))` averages the per-region counts; - an annotation name that clashes with a model field raises `ValueError`; - `alias()` works like `annotate()` but does not add the value to the `SELECT` list, which is handy when you only filter on it. ## `annotate()` is not only for aggregates `annotate()` accepts any expression, not just aggregates, and the two kinds behave differently: - a **non-aggregate** annotation such as `annotate(net=F("amount") - F("discount"))` or `annotate(month=TruncMonth("placed_at"))` computes a value per row and adds **no** `GROUP BY`; - an **aggregate** annotation such as `Count("customers")` makes Django group by the model's primary key (or by the fields named in an earlier `values()`); - you can mix both, and later annotations can reference earlier ones: `annotate(n=Count("orders")).annotate(big=Case(When(n__gte=10, then=True), default=False))`. `aggregate()` has no such split: everything in it is summarised into the single dict, and a non-aggregate expression there raises `TypeError` because it is not an aggregate. ## Empty sets and `None` Aggregates on no rows return SQL `NULL`, which Django turns into `None`: - `Order.objects.none().aggregate(Sum("amount"))` returns `{'amount__sum': None}`; - `Sum("amount", default=0)` returns `0` instead; the `default` argument wraps the aggregate in `Coalesce`; - **`Count` always returns `0`** for an empty set or group and does not accept `default` at all. ## Interview pitfalls - Saying `aggregate()` can be followed by `.filter()`: it returns a dict, so filtering must come **before** it. - Assuming a `Sum` total is never `None`: empty inputs give `None` unless `default=` is set, which breaks arithmetic in templates and serializers. - Forgetting that an `annotate()` with an aggregate changes the SQL to a grouped query, which matters once a second multi-valued relation is joined. - Reaching for `annotate()` when one number is needed; the per-row query does more work than a single `aggregate()`. ## Which one to reach for 1. You need **one number** (total revenue, average order value) for a report: `aggregate()`. 2. You need **a number per row** (orders per customer, revenue per region) to display in a list or to filter by: `annotate()`. 3. You need **a number per group of rows** that are not model instances (revenue per month): `values(...)` followed by `annotate()`, which is covered by the GROUP BY question. A common junior mistake is to loop over customers in Python calling `customer.orders.count()` for each; `annotate()` gets all the counts in one query.
- In Django, can you call aggregate() on top of an annotate()?Yes. `Region.objects.annotate(n=Count("customers")).aggregate(Avg("n"))` first computes a count per region, then averages those counts, returning a dict like `{'n__avg': 12.5}`. The aggregate can reference any alias defined by an earlier `annotate()`.
- In Django, why does Order.objects.aggregate(Sum(F("amount") * 2)) raise TypeError?`aggregate()` needs a key for each value. A plain `Sum("amount")` has the default alias `amount__sum`, but an expression over several parts has none, so Django raises `TypeError` asking for an alias. Pass it as a keyword: `aggregate(double=Sum(F("amount") * 2))`.
saying these in an interview costs you the question
- aggregate() returns a QuerySet you can keep filtering
- annotate() runs its query as soon as it is called
- Sum() on an empty QuerySet returns 0 by default
- annotate() collapses the QuerySet into a single row
- Filtering on an annotation is done in Python after fetching