Aggregation & Annotation

Everything so far has returned QuerySets of full rows — tasks, projects, tags. Sometimes what you actually want isn't the rows themselves but a number describing them: how many tasks exist in total, how many each project has, which projects are busiest. Django's ORM handles this without raw SQL through two related but distinctly different tools: aggregation and annotation. Mixing these two up is one of the most common points of confusion with the ORM, so this lesson draws the line between them clearly.

Aggregation: One Summary Value for the Whole QuerySet

Aggregation collapses an entire QuerySet down into a single summary number — a count, a sum, an average — using functions imported from django.db.models: Count, Sum, Avg, Max, and Min. You call .aggregate() on a QuerySet, and it hands back a plain dictionary with the result, not more rows.

from django.db.models import Count, Avg

# A single number: how many Task rows exist in total
Task.objects.aggregate(Count('id'))
# -> {'id__count': 47}

# Aggregation can follow a filter too — it just summarizes whatever
# QuerySet it's called on, filtered or not
Task.objects.filter(completed=True).aggregate(Count('id'))
# -> {'id__count': 31}

The key trait of .aggregate(): no matter how many rows the QuerySet started with, you get back one result describing all of them together.

Annotation: A Computed Value Added to EACH Row

Annotation uses the exact same aggregate functions, but attaches the computed value to every individual row in the QuerySet instead of collapsing them into one number. Where .aggregate() answers "what's the total?", .annotate() answers "what's this value, per row?" — you still get back a full QuerySet, just with an extra field on each object.

The clearest TaskFlow example: counting how many tasks each Project has, using the related_name='tasks' set up back in ForeignKey Relationships:

from django.db.models import Count

# Every Project, each one now carrying its own task_count attribute
projects = Project.objects.annotate(task_count=Count('tasks'))

for project in projects:
    print(project.name, project.task_count)
# Website Redesign     3
# Mobile App Launch     7
# Internal Tooling      2

Count('tasks') works here because 'tasks' is the reverse relationship name from Project to Task — Django counts, for each Project row, how many related Task rows it has, and stores that number on a new attribute named task_count. That attribute name is entirely up to you; it becomes a keyword argument on .annotate().

Filtering and Ordering by an Annotated Value

Because .annotate() still returns a normal QuerySet, the annotated value behaves like any other field — you can order by it, or filter on it. This is where annotation turns into a genuinely useful reporting tool rather than just a display detail:

# The busiest projects first — most tasks at the top
Project.objects.annotate(task_count=Count('tasks')).order_by('-task_count')

# Only projects with more than 5 tasks — filtering on the annotated value
Project.objects.annotate(task_count=Count('tasks')).filter(task_count__gt=5)

The -task_count minus sign works exactly like it does for any other order_by() field — descending order, most tasks first.

Why This Matters Beyond a Single Query

This combination — annotate a queryset, then order or filter by the annotated value — is genuinely one of the ORM's more powerful capabilities. It's exactly the pattern behind dashboard and reporting-style views: "top 5 busiest projects," "average priority of open tasks," "tag with the most tasks attached" — all of it expressed as a single ORM query, with no raw SQL required.