skip to content

In Django, why does Region.objects.annotate(reps=Count("sales_reps"), revenue=Sum("customers__orders__amount")) return inflated numbers, and how do you fix it?

level: seniorimportance: must knowfreq 55%

answer

  1. two multi-valued joins in one query
  2. rows multiply before GROUP BY
  3. distinct fixes Count, not Sum
  4. move one aggregate into a Subquery

basics

~10 s

Both aggregates hang off one SQL query that joins two independent multi-valued relations, so each region's rows are multiplied (reps × orders) before grouping. Count(distinct=True) repairs the count; the Sum needs its own Subquery.

solid answer

~40 s

`annotate()` with two aggregates over different reverse relations becomes one `SELECT` with two `LEFT OUTER JOIN`s and one `GROUP BY region.id`. A region with 3 sales reps and 40 orders produces 3 × 40 = 120 joined rows, so `Count("sales_reps")` returns 120 and `Sum` adds every order amount three times. Django documents this as a known limitation of combining aggregations with joins. For counts, `Count("sales_reps", distinct=True)` counts distinct rep ids and is correct. For sums, `distinct=True` is wrong because it drops equal amounts, so compute the revenue in a correlated `Subquery` with `OuterRef("pk")`, grouped by `values("customer__region")`, or run two separate queries. Inspect `str(qs.query)` when two aggregates share a query.

code

python · 22 lines
python
from django.db import models


class Region(models.Model):
    name = models.CharField(max_length=100)


class SalesRep(models.Model):
    region = models.ForeignKey(Region, on_delete=models.CASCADE, related_name="sales_reps")
    name = models.CharField(max_length=100)


class Customer(models.Model):
    region = models.ForeignKey(Region, on_delete=models.PROTECT, related_name="customers")
    name = models.CharField(max_length=200)


class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.PROTECT, related_name="orders")
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    status = models.CharField(max_length=20)  # "paid", "refunded", ...
    placed_at = models.DateTimeField()

go deeper

for a junior

Recall that each aggregated relation in annotate() adds a join, and that joins can create more rows than you expect.

for a middle

Explain the arithmetic: reps times orders rows per region, and why Count(distinct=True) repairs a count but not a sum.

for a senior

Rewrite the query with a correlated Subquery for the sum, verify it with str(qs.query), and add fixtures with several rows on each branch.

for a principal

Set review rules for reporting queries with several aggregates, and decide when to move such totals to precomputed tables instead.

## What goes wrong In Django's ORM, each relation you aggregate over in `annotate()` adds a **join** to the same SQL statement. When two of those relations are **multi-valued** (reverse foreign keys or many-to-many) and **independent of each other**, the database builds every combination of their rows before grouping. This is the join multiplication, sometimes called a fan-out, that the Django docs list under "Combining multiple aggregations" with a pointer to a long-standing ticket. With the models below, a region has sales reps and, through its customers, orders: ```python Region.objects.annotate( reps=Count("sales_reps"), revenue=Sum("customers__orders__amount"), ) ``` Django emits roughly: ```sql SELECT region.id, region.name, COUNT(salesrep.id) AS reps, SUM("order".amount) AS revenue FROM region LEFT OUTER JOIN salesrep ON salesrep.region_id = region.id LEFT OUTER JOIN customer ON customer.region_id = region.id LEFT OUTER JOIN "order" ON "order".customer_id = customer.id GROUP BY region.id, region.name; ``` For a region with **3 reps** and **40 orders**, the joins produce **120 rows**: - `COUNT(salesrep.id)` counts 120 instead of 3; - `SUM(amount)` adds each order three times, so revenue is tripled; - a region with no reps is not multiplied, so the error is uneven and hard to spot on a dashboard. A single aggregate over a chain (`Sum("customers__orders__amount")` alone) is fine: each order row appears once per region. The bug needs two independent branches. ## Fixing the count: `distinct=True` `Count`, `Sum` and `Avg` accept `distinct=True`. For a count it is exactly right, because what you want is the number of **distinct rep ids**: ```python reps=Count("sales_reps", distinct=True) ``` This is the fix the documentation itself shows. It costs a `COUNT(DISTINCT ...)`, which is slower than a plain count on large joins but correct. ## Why `distinct=True` does not fix the sum `Sum("customers__orders__amount", distinct=True)` sums **distinct amounts**, not distinct orders. Two orders of 250.00 in the same region count once, so the total is now too **low**. The same applies to `Avg(distinct=True)`. For values that are not identifiers, move the aggregate out of the shared join. ## Fixing the sum: a correlated `Subquery` Compute revenue in its own subquery, correlated to the outer region with `OuterRef`: ```python from django.db.models import OuterRef, Subquery, Sum revenue = ( Order.objects.filter(customer__region=OuterRef("pk")) .order_by() .values("customer__region") .annotate(total=Sum("amount")) .values("total") ) Region.objects.annotate( reps=Count("sales_reps", distinct=True), revenue=Subquery(revenue), ) ``` The pattern follows the documented recipe for aggregates inside a subquery: 1. `filter(... = OuterRef("pk"))` ties the inner query to the current region; 2. `order_by()` clears any default ordering so it cannot join the grouping; 3. `values("customer__region")` groups by region; 4. `annotate(total=Sum("amount"))` computes the sum; 5. `values("total")` leaves exactly one column, as a `Subquery` annotation requires. Regions without orders get `None`; wrap the subquery in `Coalesce(..., Value(Decimal("0")))` if the report needs zero. ## Confirming the bug in a Django shell 1. Pick one region and compute the truth with separate queries: `region.sales_reps.count()` and `Order.objects.filter(customer__region=region).aggregate(Sum("amount"))`. 2. Compare with the annotated values from the combined query; a ratio equal to the other relation's row count is the signature of multiplication. 3. Print `str(qs.query)` and count the `LEFT OUTER JOIN`s that go through reverse relations; two independent ones mean trouble. 4. After the fix, keep a regression test whose fixture has at least two rows on each branch. The same multiplication appears with a **filter on one multi-valued relation plus an aggregate over another**, e.g. `Region.objects.filter(sales_reps__active=True).annotate(Sum("customers__orders__amount"))`: every active rep multiplies the orders. Moving the filter into `Exists(...)` avoids the join. ## Choosing a fix | Situation | Fix | |---|---| | counting related rows | `Count(..., distinct=True)` | | summing or averaging values next to another multi-valued join | `Subquery` with `OuterRef` | | several independent totals for one screen | separate queries, or one `Subquery` per total | | unsure whether rows multiply | print `str(qs.query)` and count the joins | A useful habit in code review: any `annotate()` with **two or more aggregates across different reverse relations** is suspect until proven otherwise, and a test with more than one related row on each side will expose it where a single-row fixture hides it.

  • In Django, why does a test with one sales rep and one order per region not catch this bug?
    With one row on each side the join produces 1 × 1 = 1 row per region, so every aggregate is correct. The multiplication only appears when both relations have more than one row. Fixtures for aggregate queries should include several related rows on each branch.
  • In Django, is Count("customers__orders", distinct=True) needed for a single chained relation?
    Not for the chain alone: each order row appears once per region, so a plain count is right. `distinct=True` becomes necessary as soon as another multi-valued join, or a filter on a multi-valued relation, is added to the same query.

It is like counting a family's children from photos in which every child is pictured with every pet: three children and two dogs give six photos. Counting distinct faces gives the right number of children; adding up heights from the photos still double counts.

saying these in an interview costs you the question

  • Adding distinct=True to the Sum fixes the revenue total
  • Django runs one query per annotation, so they cannot interfere
  • Join multiplication only happens with many-to-many fields
  • select_related() prevents the extra rows in annotations
  • The inflated numbers mean the GROUP BY clause is missing