Aggregation & Annotation

Aggregation is the process of folding a whole queryset of rows down to summary numbers — counts, sums, averages, maximums, and minimums — that describe the set as a whole rather than any single row.

Learn Aggregation & Annotation in our free Django course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick…

Part of the free Django course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

In this lesson you'll use aggregate() to collapse a queryset into one dict of totals, annotate() to attach a computed value to every row and to group rows into per-category summaries, and the filter() and filter= tools that control exactly which rows and groups count.

When you want a single number that describes a whole queryset — the average price, the total stock, the highest score — you reach for aggregate() . It runs over every matching row and returns a plain dictionary of results, not a queryset. You pass it aggregate functions like Count , Sum , Avg , Max , and Min .

The default key is field__function (so Avg("price") becomes price__avg ), but passing a keyword like average=Avg("price") lets you name the result yourself. aggregate() always evaluates the queryset immediately and hands back the dict.

Where aggregate() returns one dict for the whole set, annotate() attaches a computed value to each row and returns a queryset you can keep chaining. Annotate an author with their book count, then read author.num_books on every result.

The real power comes from pairing values() with annotate() to build a GROUP BY . values("category") tells Django to group rows by category, and the aggregate inside annotate() is computed once per group:

Where you place filter() relative to annotate() changes its meaning entirely. filter() before the aggregate restricts which rows are counted (a SQL WHERE ). filter() after annotate() tests the computed value and keeps only qualifying groups (a SQL HAVING ).

Fill in the blank so each category group is totalled, just like values("category").annotate(total=Sum("price")) .

❌ "Cannot resolve keyword 'num' into field" when filtering

You tried to filter(num__gte=3) before defining num with annotate() .

✅ Fix: annotate first, then filter — .annotate(num=Count("id")).filter(num__gte=3) .

❌ My group counts are way too high (duplicated rows)

Aggregating across a join with multiple related rows multiplies the count.

✅ Fix: pass distinct=True to the aggregate, e.g. Count("book", distinct=True) .

❌ aggregate() gave me a dict but I tried to loop over rows

aggregate() returns a single dict, not a queryset, so there are no rows to iterate.

✅ Fix: use values(...).annotate(...) when you need one summary row per group instead.

Write the report that values("category").annotate(num=Count, avg=Avg, max=Max) would produce: a count, a rounded average price, and the priciest book for each category.

Lesson complete — you can summarise data with the ORM!

You can now collapse a queryset to a dict with aggregate() , attach per-row values and build a GROUP BY with values().annotate() , and control results with filter() before (WHERE), filter() after (HAVING), and the filter= conditional aggregate.

🚀 Up next: Query Performance — make these aggregate queries fast with indexes and fewer database round-trips.

Practice quiz

What does aggregate() return?

  • A queryset of rows
  • A list of tuples
  • A single dictionary of summary values
  • A model instance

Answer: A single dictionary of summary values. aggregate() is terminal: it collapses the whole queryset into one dict and returns it immediately.

What does annotate() do?

  • Returns one dict for the whole set
  • Attaches a computed value to each row
  • Deletes matching rows
  • Replaces a model field

Answer: Attaches a computed value to each row. annotate() adds a computed value per row and returns a chainable queryset.

How do you produce a SQL GROUP BY in the ORM?

  • Use only aggregate()
  • Call group_by()
  • Call order_by() first
  • Chain values() before annotate()

Answer: Chain values() before annotate(). values('category').annotate(...) groups by the values() fields, generating GROUP BY.

Which is the default key for Avg('price') in aggregate()?

  • price__avg
  • avg_price
  • price_avg
  • average

Answer: price__avg. The default key is field__function, so Avg('price') becomes price__avg.

filter() AFTER annotate() corresponds to which SQL clause?

  • WHERE
  • HAVING
  • ORDER BY
  • JOIN

Answer: HAVING. Filtering on the aggregate result after annotate() becomes a HAVING clause.

filter() BEFORE annotate() corresponds to which SQL clause?

  • HAVING
  • GROUP BY
  • WHERE
  • LIMIT

Answer: WHERE. Filtering rows before aggregating restricts the input rows, which is a WHERE clause.

What does Count('id', filter=Q(in_stock=True)) do?

  • Removes out-of-stock groups
  • Raises an error
  • Sorts by stock
  • Counts only in-stock rows per group, keeping every group

Answer: Counts only in-stock rows per group, keeping every group. The filter= argument is a conditional aggregate that counts only matching rows.

Which functions are aggregate functions you import from django.db.models?

  • Count, Sum, Avg, Max, Min
  • filter, exclude, get
  • values, annotate, order_by
  • save, delete, create

Answer: Count, Sum, Avg, Max, Min. Count, Sum, Avg, Max, and Min are the aggregate functions used by aggregate/annotate.

How do you avoid inflated counts when aggregating across a join?

  • Use aggregate() twice
  • Add order_by()
  • Pass distinct=True to the aggregate
  • Use values() only

Answer: Pass distinct=True to the aggregate. Count('book', distinct=True) avoids duplicate counting across multi-row joins.

Why can't you loop over the result of aggregate()?

  • It is async
  • It returns a single dict, not a queryset
  • It returns None
  • It returns a string

Answer: It returns a single dict, not a queryset. aggregate() returns one dict; use values().annotate() for one summary row per group.