Aggregation & Annotation

## 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.