Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 77 ยท Models and the ORM

Aggregation and Annotation

Summarize data in the Django ORM with aggregate() and annotate(), group rows with values(), label them with Case/When, and use Subquery, Exists and Window.

Sooner or later every application needs numbers, not just rows: how many books each author wrote, the average price, the best seller of each month. You could loop over objects in Python and add things up, but that loads every row into memory and is slow. The database is far better at counting and summing, and Django lets you ask for it without writing SQL.

Two methods do most of the work. aggregate() collapses a whole queryset into a single summary, and annotate() attaches a calculated value to every object. In this lesson you will use both, learn how values() turns an annotation into a GROUP BY, count conditionally, build labels with Case/When, and meet the heavier tools: Subquery, Exists and window functions. The examples use the Author, Book and Sale relations from Relationships, so skim that lesson first if reverse lookups like books__pages look unfamiliar.

Each example installs Django in your browser and builds a fresh in-memory SQLite database from the same models. The setup lines at the top are always the same; the interesting part starts at the model definitions.

aggregate versus annotate

Both methods take aggregate expressions such as Count, Sum, Avg, Min and Max. The difference is the shape of the answer.

flowchart LR
    Q["Book.objects"] --> A["aggregate(Avg('price'))"]
    Q --> B["annotate(n=Count('sales'))"]
    Q --> C["values('year').annotate(n=Count('id'))"]
    A --> A2["one dict:<br/>{'price__avg': 11.9}"]
    B --> B2["every book,<br/>plus book.n"]
    C --> C2["one dict<br/>per year (GROUP BY)"]

The same queryset can yield one summary, the same rows with an extra attribute, or one row per group.

aggregate() is a terminal method: it runs the query immediately and returns a plain dictionary. Django names each key <field>__<function> (for example id__count) unless you pass a keyword argument to choose the name. annotate() is lazy and returns another queryset, so you can keep filtering, ordering and slicing it.

Python aggregate() and annotate() side by side

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Some details worth noticing:

  • Count("id") counts rows; Count("books") on Author counts related rows. An author with no books gets 0, because Django uses a LEFT OUTER JOIN for the relation.
  • Sum and Avg return None for an empty set (SQL has no sum of nothing). Wrap them in Coalesce(Sum(...), 0) when you need a number.
  • You can aggregate over an annotation (Max("n") above), which is how you ask for "the highest count".
  • The annotation name must not clash with a field or reverse accessor on the model. That is why we write book_count, not books.

Grouping with values()

An annotation on a queryset of model objects groups by object: each author gets their own count. To group by something else, such as a year, call values() first with the grouping columns, then annotate(). Django puts the values() fields in the GROUP BY clause, and the result is a queryset of dictionaries.

The order of the calls matters. values().annotate() groups, while annotate().values() first computes per-object values and then merely selects columns. The same goes for filter():

  • filter() before annotate() restricts which rows are counted.
  • filter() after annotate() filters on the result (it becomes HAVING in SQL).
Python Group by, conditional counts and Case/When

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output carefully:

  • The decade grouping is (F("year") / 10) * 10. F() refers to a column inside the database, and with two integers the division is an integer division, so 1974 becomes 1970.
  • The first author query filters books before counting, so Atwood keeps 3 books but Le Guin drops to 1, and the author without books vanishes. The conditional version, Count("books", filter=Q(books__pages__gt=300)), keeps every author and counts only the long books. This is usually what you want in a report.
  • Case/When is SQL's CASE WHEN. Each When takes lookups and a then, and default is the ELSE branch. Give it an output_field when Django cannot guess the type.
  • Counting two different relations in one query multiplies rows, because the database joins books to sales before counting. Each book is repeated once per sale, so Count("books") is inflated (Le Guin shows 4 books, not 3). Use distinct=True for the count that should not be multiplied, or compute the second number in a subquery.

Warning: If you annotate with two aggregates over different relations and one result looks too large, suspect a join multiplication. Add distinct=True, or move one of the numbers into a Subquery.

Subquery and Exists

Sometimes the value you need is not a simple aggregate: "the title of each author's newest book" or "does this author have any sales?". For these, Django lets you embed one queryset inside another with Subquery and Exists.

The inner queryset refers to the current outer row with OuterRef. A Subquery must produce exactly one column and at most one row, which is why the inner query ends in .values("title")[:1]. Exists only asks whether any row exists, and the database can stop at the first match, so it is cheaper than counting and comparing with zero. Negate it with ~Exists(...).

Window functions

A window function computes a value across a set of related rows without collapsing them. Where annotate(Count(...)) gives one row per group, a window gives every row its own value that was calculated over its group, such as a rank or a running total. You build one with Window(expression=..., partition_by=..., order_by=...). partition_by plays the role of the group and order_by decides the order inside it. Common expressions are Rank(), DenseRank(), RowNumber(), Lag(), Lead() and any aggregate such as Sum("pages"), which gives a running total when you add an order_by.

Python Subquery, Exists and Window

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The final query filters directly on the window annotation, which Django supports from version 4.2 on by wrapping the query in a subquery. Window functions need a database that supports them. SQLite does (since 3.25), as do PostgreSQL, MySQL 8 and Oracle.

Choosing the right tool

Question Tool
One number for the whole queryset aggregate()
A number next to every object annotate()
One row per category values("category").annotate(...)
Count only some related rows Count(..., filter=Q(...))
A label based on conditions Case / When
A value from a related query Subquery + OuterRef
"Is there at least one...?" Exists
Rank or running total per group Window

Annotations are computed by the database, so you can filter and order by them like real fields. They also keep your code to a single query, where a Python loop would need one query per object (the "N+1" problem from the performance lesson).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Book.objects.aggregate(Avg('price')) return?

    aggregate() runs immediately and returns one dictionary. The key is built from the field and function name unless you choose a name with a keyword argument.
  2. 2. Which call produces one result row per publication year?

    Calling values() first puts the column in GROUP BY, so annotate() counts per year. The other order annotates each book first and only then picks the column.
  3. 3. You annotate authors with Count('books') and Count('books__sales') and the book count is too high. What is the usual fix?

    Joining two relations repeats each book once per sale. distinct=True inside Count counts every book only once.
  4. 4. An author has no books. What does Author.objects.annotate(n=Count('books')) give for that author?

    Django uses a LEFT OUTER JOIN for this relation, so authors without books are kept and Count gives 0. Sum and Avg would give None.

Summary

  • aggregate() runs at once and returns a dictionary; annotate() is lazy and adds an attribute to each row.
  • Name results with keyword arguments, and avoid names that clash with model fields or reverse accessors.
  • values("x").annotate(...) is GROUP BY; filters before annotate() restrict the counted rows, filters after it act like HAVING.
  • Count(..., filter=Q(...)) counts conditionally without dropping rows; distinct=True protects against join multiplication.
  • Case/When builds computed labels and values; Coalesce turns None into a default.
  • Subquery with OuterRef fetches one value per row, Exists asks a yes/no question, and Window ranks or accumulates without collapsing rows.

Next, see how to keep these queries fast in ORM Performance: N+1, select_related, prefetch_related.