Aggregation & Annotation

Reviewed & published by Brayan K

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.

Part of the free Django course at LearnCodingFast — hands-on lessons with worked examples and the output they print, 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.

What You'll Learn in This Lesson

• Collapsing a queryset with aggregate()
• The Count, Sum, Avg, Max, and Min functions
• Attaching computed values per row with annotate()
• Grouping with values().annotate() (SQL GROUP BY)
• Filtering rows before vs. groups after annotate()
• Conditional aggregates with the filter= argument

1 aggregate(): Collapse a QuerySet to One Dict

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.

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

# A single summary value: the average price of all books
Book.objects.aggregate(Avg("price"))
# {'price__avg': 37.5}

# Several summaries at once, with your own key names
Book.objects.aggregate(
    count=Count("id"),
    total=Sum("price"),
    average=Avg("price"),
    cheapest=Min("price"),
    priciest=Max("price"),
)
# {'count': 4, 'total': 150, 'average': 37.5,
#  'cheapest': 20, 'priciest': 55}

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.

# aggregate() collapses an entire queryset into a SINGLE dict of summary
# values. Book.objects.aggregate(Avg("price")) is just "scan every row,
# fold them into one number." We do the same fold over a list of dicts.

books = [
    {"title": "A", "price": 30, "pages": 200},
    {"title": "B", "price": 45, "pages": 350},
    {"title": "C", "price": 20, "pages": 150},
    {"title": "D", "price": 55, "pages": 400},
]

prices = [b["price"] for b in books]

summary = {
    "count":     len(prices),
    "price__sum": sum(prices),
    "price__avg": round(sum(prices) / len(prices), 2),
    "price__max": max(prices),
    "price__min": min(prices),
}

print(summary)

# EXPECTED:
# {'count': 4, 'price__sum': 150, 'price__avg': 37.5, 'price__max': 55, 'price__min': 20}

# ✅ Expected output:
# {'count': 4, 'price__sum': 150, 'price__avg': 37.5, 'price__max': 55, 'price__min': 20}

2 annotate(): A Computed Value per Row & GROUP BY

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.

from django.db.models import Count

# One extra computed column per Author row
authors = Author.objects.annotate(num_books=Count("book"))
for author in authors:
    print(author.name, author.num_books)

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:

from django.db.models import Count, Sum

# Group by category, count and total each group
Book.objects.values("category").annotate(
    num=Count("id"),
    total=Sum("price"),
).order_by("category")
# <QuerySet [
#   {'category': 'fiction', 'num': 2, 'total': 100},
#   {'category': 'tech',    'num': 3, 'total': 75},
# ]>

# annotate() attaches a computed value to EACH row, and when you pair
# values("category").annotate(Count("id")) it GROUPS rows by category
# first. Here is that group-by + per-group count in plain Python.

books = [
    {"title": "A", "category": "tech",    "price": 30},
    {"title": "B", "category": "fiction", "price": 45},
    {"title": "C", "category": "tech",    "price": 20},
    {"title": "D", "category": "fiction", "price": 55},
    {"title": "E", "category": "tech",    "price": 25},
]

def group_by(rows, key):
    groups = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row)
    return groups

groups = group_by(books, "category")

# values("category").annotate(num=Count("id"), total=Sum("price"))
for category in sorted(groups):
    rows = groups[category]
    num = len(rows)
    total = sum(r["price"] for r in rows)
    print(f"{category}: num={num}, total={total}")

# EXPECTED:
# fiction: num=2, total=100
# tech: num=3, total=75

# ✅ Expected output:
# fiction: num=2, total=100
# tech: num=3, total=75

3 Filtering Aggregates: WHERE vs. HAVING vs. filter=

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

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

# (1) WHERE: filter rows BEFORE aggregating
Book.objects.filter(in_stock=True).aggregate(Avg("price"))
# only in-stock books feed the average

# (2) HAVING: filter on the AGGREGATE, after annotate()
Book.objects.values("category").annotate(
    num=Count("id"),
).filter(num__gte=3)
# keeps only categories with 3 or more books

# (3) filter= argument: a CONDITIONAL aggregate
Book.objects.values("category").annotate(
    stocked=Count("id", filter=Q(in_stock=True)),
)
# counts only in-stock books per group, keeps every group

• filter() before aggregating → WHERE: shrinks the rows that enter the calculation.
• filter() after annotate() → HAVING: drops whole groups by their aggregate.
• Count("id", filter=Q(...)) → a conditional aggregate that counts only matching rows.
• Order matters: the same filter() means different things on each side of annotate().

# Two ways to filter with aggregates:
#  (1) filter BEFORE aggregating  -> WHERE: shrinks the rows first
#  (2) filter AFTER annotate      -> HAVING: keeps only groups that qualify
# We show both, plus the filter= argument (a conditional aggregate).

books = [
    {"category": "tech",    "price": 30, "in_stock": True},
    {"category": "fiction", "price": 45, "in_stock": False},
    {"category": "tech",    "price": 20, "in_stock": True},
    {"category": "fiction", "price": 55, "in_stock": True},
    {"category": "tech",    "price": 25, "in_stock": False},
]

def group_by(rows, key):
    groups = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row)
    return groups

# (1) WHERE: filter(in_stock=True) BEFORE aggregating
in_stock = [b for b in books if b["in_stock"]]
print("avg price in stock:", round(sum(b["price"] for b in in_stock) / len(in_stock), 2))

# (2) HAVING: annotate per group, then keep groups with num >= 3
counts = {cat: len(rows) for cat, rows in group_by(books, "category").items()}
big = {cat: n for cat, n in counts.items() if n >= 3}
print("groups with 3+ books:", big)

# (3) filter= argument: Count("id", filter=Q(in_stock=True)) per group
for cat in sorted(group_by(books, "category")):
    rows = group_by(books, "category")[cat]
    stocked = sum(1 for r in rows if r["in_stock"])
    print(f"{cat}: stocked={stocked}")

# EXPECTED:
# avg price in stock: 35.0
# groups with 3+ books: {'tech': 3}
# fiction: stocked=1
# tech: stocked=2

# ✅ Expected output:
# avg price in stock: 35.0
# groups with 3+ books: {'tech': 3}
# fiction: stocked=1
# tech: stocked=2

🎯 Your Turn: Sum Each Group

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

# 🎯 YOUR TURN: replace ___ so we SUM the prices in each category group.
# This mirrors values("category").annotate(total=Sum("price")).

books = [
    {"category": "tech",    "price": 30},
    {"category": "fiction", "price": 45},
    {"category": "tech",    "price": 20},
    {"category": "fiction", "price": 55},
]

groups = {}
for book in books:
    groups.setdefault(book["category"], []).append(book["price"])

for category in sorted(groups):
    total = ___(groups[category])      # add up the prices in this group
    print(category, total)

# EXPECTED OUTPUT:
# fiction 100
# tech 50

Common Errors (And How to Fix Them)

❌ "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.

📋 Quick Reference

🏆 Mini Challenge: Build a Per-Category Report

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.

# 🏆 Mini Challenge: build a full report like
# values("category").annotate(num=Count, avg=Avg, max=Max) does.
# For each category, report the count, the rounded average price,
# and the most expensive book's price.

books = [
    {"category": "tech",    "price": 30},
    {"category": "fiction", "price": 45},
    {"category": "tech",    "price": 20},
    {"category": "fiction", "price": 55},
    {"category": "tech",    "price": 28},
]

def group_by(rows, key):
    groups = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row)
    return groups

def report(rows):
    result = {}
    for category, group in group_by(rows, "category").items():
        prices = [r["price"] for r in group]
        result[category] = {
            "num": len(prices),
            "avg": round(sum(prices) / len(prices), 2),
            "max": max(prices),
        }
    return result

for category in sorted(report(books)):
    stats = report(books)[category]
    print(f"{category}: num={stats['num']}, avg={stats['avg']}, max={stats['max']}")

# EXPECTED:
# fiction: num=2, avg=50.0, max=55
# tech: num=3, avg=26.0, max=30

❓ Frequently Asked Questions

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.

Practice quiz

Continue this course

• Previous: QuerySet Deep Dive: Q, F & Lookups
• Next: Query Performance: select_related & prefetch_related — Kill the N+1 query problem and write fast, efficient ORM code
• Quick reference: Django cheat sheet

Frequently asked questions

What is the difference between aggregate() and annotate()?

aggregate() collapses an entire queryset down to a single dictionary of summary values — one Count, one Sum, one Avg for the whole set. annotate() instead attaches a computed value to each row of the queryset, and when combined with values() it groups rows and produces one summary row per group. In short, aggregate() returns a dict, annotate() returns a queryset.

How do I do a GROUP BY in Django?

You chain values() before annotate(). Calling Book.objects.values('category').annotate(num=Count('id')) tells Django to group rows by category and count the rows in each group, which generates a SQL GROUP BY category. The fields you list in values() become the grouping columns, and each aggregate in annotate() becomes a computed column per group.

What is the difference between filtering before and after annotate()?

Calling filter() before annotate() restricts which rows enter the aggregation — it becomes a SQL WHERE clause that shrinks the input. Calling filter() after annotate() filters on the aggregate result itself, keeping only groups whose computed value passes the test — this becomes a SQL HAVING clause. So filter-before narrows the rows, filter-after narrows the groups.

What does the filter= argument on an aggregate do?

The filter= argument lets you build a conditional aggregate, counting or summing only the rows that match a condition without removing other rows from the result. For example Count('id', filter=Q(in_stock=True)) counts just the in-stock books while still returning every group. It is the ORM's way of expressing a SQL conditional aggregate like COUNT(CASE WHEN ...).