Aggregation and annotation: reporting with the ORM

Sooner or later every real application needs to summarise data, not just list it: how much revenue this month, how many appointments per doctor, the average test turnaround. Doing that by loading rows and adding them up in Python is slow and wrong-headed — the database is built for exactly this, and Django's aggregate() and annotate() let you ask it directly. This lesson is where the ORM stops being a row-fetcher and becomes a reporting tool, which is a large part of why teams choose Django.

Two questions, two methods

There are two shapes of summary, and Django has one method for each:

• aggregate() — collapse a whole queryset to a single summary row. "What is the total revenue?" returns one number.
• annotate() — attach a computed value to each object in a queryset. "How many appointments does each doctor have?" returns every doctor, each with a count.

Hold that distinction: aggregate gives you one answer for the set; annotate gives you an answer per row. Nearly every reporting need is one or the other.

aggregate: one summary for the whole set

Pass aggregation functions and get back a dictionary of results:

from django.db.models import Count, Sum, Avg

Appointment.objects.aggregate(total=Sum("fee"), avg=Avg("fee"), n=Count("id"))
# verified: {'total': Decimal('3500'), 'avg': Decimal('700'), 'n': 5}

One query to the database, one row back. The keys (total, avg, n) are names you choose; the values are computed in the database with SQL SUM, AVG, COUNT. Five appointments with fees 500 to 900 sum to 3500 and average 700 — computed by the database, not by pulling five rows into Python. You can filter first and aggregate the result: Appointment.objects.filter(status="done").aggregate(Sum("fee")) totals only completed appointments. This is how you build every "total", "count", "average" figure a dashboard needs.

annotate: a computed value per object

annotate adds a computed field to each row of a queryset, most powerfully across a relationship. "How many appointments, and how much revenue, does each doctor have?":

from django.db.models import Count, Sum

doctors = Doctor.objects.annotate(
    n=Count("appointments"),
    revenue=Sum("appointments__fee"),
)
for d in doctors:
    print(d.name, d.n, d.revenue)
# verified:
#   Dr Iyer  2  1400
#   Dr Rao   3  2100

Each Doctor object now carries .n and .revenue attributes that did not exist on the model — they were computed by the database with a GROUP BY and joined onto each row. Dr Rao's three appointments (fees 500, 700, 900) total 2100; Dr Iyer's two (600, 800) total 1400 — and crucially this is one query for all doctors, not one per doctor. annotate is how you avoid the N+1 trap for aggregates: instead of looping doctors and counting each one's appointments (N+1), you annotate once and the database does the grouping.

The appointments__fee spans the reverse relationship (related_name="appointments") — annotation follows relationships with the same __ syntax as filtering.

Combining with filter, order and values

Annotations are ordinary queryset values, so you compose with everything you know:

# doctors with more than 2 appointments, busiest first
Doctor.objects.annotate(n=Count("appointments")).filter(n__gt=2).order_by("-n")

# revenue per city, as plain dicts
Appointment.objects.values("patient__city").annotate(revenue=Sum("fee")).order_by("-revenue")

Two patterns worth internalising. First, you can filter on an annotation (filter(n__gt=2)) — that becomes a SQL HAVING clause, letting you ask "only the groups where the aggregate exceeds X". Second, values(...).annotate(...) is the group-by-a-field idiom: values("patient__city") groups rows by city, and the annotate computes a total per city — the standard way to produce "revenue by city", "appointments by status", any "X per Y" report.

Conditional aggregation, for "how many of each" in one query

A common real need: count bookings by status without three separate queries. Count and Sum accept a filter=:

from django.db.models import Count, Q

Appointment.objects.aggregate(
    booked=Count("id", filter=Q(status="booked")),
    done=Count("id", filter=Q(status="done")),
    cancelled=Count("id", filter=Q(status="cancelled")),
)

One query returns all three counts. This conditional aggregation is how a dashboard shows a breakdown ("120 booked, 80 done, 15 cancelled") efficiently, rather than firing a query per category.

Why this belongs in the database, not Python

The temptation, especially early, is to write sum(a.fee for a in Appointment.objects.all()). It even works — on ten rows. But it loads every row into Python to add them up, when the database can compute the sum without shipping the rows at all. On real data the difference is enormous: aggregate(Sum("fee")) transfers one number; the Python version transfers every appointment. Summarising is the database's job — do it there. The mark of someone who understands the ORM is that their reports are aggregate/annotate queries, not Python loops over .all(). This is the beginning of the ORM depth that the rest of this module builds on.

Check your work

aggregate versus annotate. aggregate collapses a queryset to one summary row (one answer for the set); annotate attaches a computed value to each object (an answer per row).

What aggregate returns. A dict of named results computed in the database — verified {'total': 3500, 'avg': 700, 'n': 5} for five fees of 500 to 900.

What annotate does across a relationship. Adds a per-row computed field via GROUP BY in one query — verified Dr Rao 3/2100, Dr Iyer 2/1400 — avoiding the N+1 you would get looping and counting.

Filtering on an annotation. filter(n__gt=2) becomes SQL HAVING — "only groups where the aggregate exceeds X".

The group-by idiom. values("field").annotate(total=Sum(...)) groups by a field and computes a total per group — "X per Y" reports.

Conditional aggregation. Count("id", filter=Q(...)) gets "how many of each" in a single query.

Why in the database. Python summation loads every row; aggregate/annotate compute in the database and transfer only the result — the correct, scalable approach.

Practice

1. Reproduce the verified aggregate(Sum, Avg, Count) on appointment fees; confirm total 3500, avg 700.
2. annotate doctors with appointment count and revenue; confirm Rao 3/2100 and Iyer 2/1400, and (with query logging) confirm it is a single query.
3. Filter on an annotation: doctors with more than two appointments. Confirm the SQL uses HAVING (via .query or connection.queries).
4. Produce "revenue per city" with values("patient__city").annotate(revenue=Sum("fee")) and read the grouped result.
5. Get booked/done/cancelled counts in one query with conditional Count(..., filter=Q(...)).
6. Write the same total two ways — aggregate(Sum("fee")) and sum(a.fee for a in Appointment.objects.all()) — and compare the query counts. Reason about which fails at scale.

Official documentation

• Django — Aggregation — The full guide to aggregate and annotate.
• Django — Count, Sum, Avg and other aggregates — Every aggregation function.
• Django — Conditional aggregation — filter= inside an aggregate.

Next: F() and Q() — database-side maths and complex lookups.