1. Introduction to Aggregation
Django’s ORM allows you to retrieve, create, update, and delete individual objects. However, many real‑world applications require summary values—such as totals, averages, or counts—computed over a collection of objects. Django provides two main tools for this: aggregate() and annotate().
Throughout this guide, we use the following models:
Author(name, age)
Publisher(name)
Book(name, pages, price, rating, authors, publisher, pubdate)
Store(name, books)
2. Quick Aggregation Cheat Sheet
Total number of books:
Book.objects.count()
Total books from a specific publisher:
Book.objects.filter(publisher__name="BaloneyPress").count()
Average price (with default if no rows exist):
Book.objects.aggregate(Avg("price", default=0))
Maximum price:
Book.objects.aggregate(Max("price", default=0))
Difference between max price and average price:
Book.objects.aggregate(
price_diff=Max("price", output_field=FloatField()) - Avg("price")
)
Count of books per publisher:
Publisher.objects.annotate(num_books=Count("book"))
Conditional counts using Q objects:
above_5 = Count("book", filter=Q(book__rating__gt=5))
below_5 = Count("book", filter=Q(book__rating__lte=5))
Publisher.objects.annotate(above_5=above_5, below_5=below_5)
Top 5 publishers by number of books:
Publisher.objects.annotate(num_books=Count("book"))
.order_by("-num_books")[:5]
3. Generating Aggregates Over an Entire QuerySet
aggregate() computes summary values for the entire QuerySet and returns a dictionary.
Example: average book price
Book.objects.aggregate(Avg("price"))
Result:
{'price__avg': 34.35}
Custom names for aggregates:
Book.objects.aggregate(average_price=Avg("price"))
Multiple aggregates:
Book.objects.aggregate(Avg("price"), Max("price"), Min("price"))
4. Per‑Object Aggregation Using annotate()
annotate() adds computed values to each object in a QuerySet.
Example: count authors per book
q = Book.objects.annotate(Count("authors"))
q[0].authors__count
Custom annotation name:
q = Book.objects.annotate(num_authors=Count("authors"))
Unlike aggregate(), annotate() returns a QuerySet and can be chained with filter(), order_by(), or additional annotate() calls.
5. Combining Multiple Aggregations
When combining multiple annotate() calls, Django performs joins that may multiply rows, producing incorrect results.
Example of incorrect behavior:
q = Book.objects.annotate(Count("authors"), Count("store"))
q[0].authors__count # Incorrect
q[0].store__count # Incorrect
This happens because the join between Book–Author and Book–Store multiplies rows.
Solution: use distinct=True
q = Book.objects.annotate(
Count("authors", distinct=True),
Count("store", distinct=True)
)
Always inspect SQL if unsure:
print(q.query)
Conclusion
Django’s aggregation tools—aggregate() and annotate()—provide powerful ways to compute summary values across QuerySets or per object. Understanding how joins affect multi‑aggregation queries and using distinct=True when necessary ensures accurate results. When in doubt, inspect the generated SQL to understand how Django constructs your query.
1. Joins and Aggregations on Related Fields
Django allows you to aggregate values from related models using the same double‑underscore syntax used in filters. Django automatically performs the necessary joins.
Example: minimum and maximum book price per store
Store.objects.annotate(
min_price=Min("books__price"),
max_price=Max("books__price")
)
Aggregating across all stores:
Store.objects.aggregate(
min_price=Min("books__price"),
max_price=Max("books__price")
)
Deep join example:
Youngest author of any book sold in any store:
Store.objects.aggregate(youngest_age=Min("books__authors__age"))
2. Traversing Reverse Relationships in Aggregations
You can also aggregate across reverse relations using the lowercase model name and double underscores.
Example: count books per publisher
Publisher.objects.annotate(Count("book"))
Oldest publication date among a publisher’s books:
Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate"))
Many‑to‑many reverse aggregation:
Total pages written by each author:
Author.objects.annotate(total_pages=Sum("book__pages"))
Average rating of books per author:
Author.objects.aggregate(average_rating=Avg("book__rating"))
3. Aggregations with filter() and exclude()
Filtering before annotate:
Filters restrict which objects are included in the annotation.
Book.objects.filter(name__startswith="Django")
.annotate(num_authors=Count("authors"))
Filtering before aggregate:
Filters restrict which objects are aggregated.
Book.objects.filter(name__startswith="Django").aggregate(Avg("price"))
4. Filtering on Annotations
You can filter using annotation aliases just like normal fields.
Books with more than one author:
Book.objects.annotate(num_authors=Count("authors"))
.filter(num_authors__gt=1)
Multiple annotations with different conditions:
highly_rated = Count("book", filter=Q(book__rating__gte=7))
Author.objects.annotate(
num_books=Count("book"),
highly_rated_books=highly_rated
)
5. Choosing Between filter Argument and QuerySet.filter()
For a single aggregation, it is more efficient to use QuerySet.filter().
The filter= argument is useful only when multiple aggregations require different conditions.
6. Order of annotate() and filter()
The order of annotate() and filter() matters because annotations are computed based on the state of the QuerySet at the moment annotate() is applied.
Example with Count:
annotate → filter:
Publisher.objects.annotate(num_books=Count("book", distinct=True))
.filter(book__rating__gt=3)
The filter does not affect the annotation.
filter → annotate:
Publisher.objects.filter(book__rating__gt=3)
.annotate(num_books=Count("book"))
Only books with rating > 3 are counted.
Example with Avg:
The first query averages all books; the second averages only filtered books.
7. Using order_by() with Annotations
You can sort QuerySets using annotation aliases.
Example:
Book.objects.annotate(num_authors=Count("authors"))
.order_by("num_authors")
Conclusion
Django ORM provides powerful tools for aggregating across related models, filtering on annotations, and controlling query behavior through the order of annotate() and filter(). Understanding these mechanics is essential for writing accurate and efficient database queries. When in doubt, inspect the generated SQL using str(queryset.query).
1. How values() Changes Aggregation Behavior
Normally, annotate() produces one result per object in the QuerySet. However, when you use values(), Django groups results by the fields listed in values(). Each unique combination becomes a group, and annotations are computed per group.
Example: average rating per author
Author.objects.annotate(average_rating=Avg("book__rating"))
This returns one row per author.
Using values():
Author.objects.values("name").annotate(average_rating=Avg("book__rating"))
Now authors are grouped by name. Authors with the same name are merged into one group.
2. Order of annotate() and values()
The order of these clauses matters.
Case 1: values() → annotate()
Grouping happens first, then annotations are computed per group.
Case 2: annotate() → values()
Annotations are computed per object, and values() only controls which fields appear in the output.
Author.objects.annotate(average_rating=Avg("book__rating"))
.values("name", "average_rating")
Here, you must explicitly include average_rating in values().
3. Interaction with order_by()
Fields used in order_by() are implicitly included in grouping, even if not listed in values(). This can cause incorrect grouping.
Example model:
class Item(models.Model):
name = models.CharField(max_length=10)
data = models.IntegerField()
Incorrect grouping:
items = Item.objects.order_by("name")
items.values("data").annotate(Count("id"))
This groups by (data, name), not just data.
Correct approach:
items.values("data").annotate(Count("id")).order_by()
Clearing ordering removes unintended grouping.
Note: Django never removes explicit order_by() clauses automatically.
4. Aggregating on Annotations
You can aggregate on annotation aliases.
Example: average number of authors per book
Book.objects.annotate(num_authors=Count("authors"))
.aggregate(Avg("num_authors"))
5. Aggregating Empty QuerySets
Aggregations on empty sets return None by default.
Example:
Book.objects.filter(name__contains="web").aggregate(Sum("price"))
# {'price__sum': None}
Using default=
Book.objects.filter(name__contains="web")
.aggregate(Sum("price", default=0))
Count always returns 0 for empty sets.
6. Aggregating with MySQL ONLY_FULL_GROUP_BY
MySQL may raise errors when mixing aggregate and non‑aggregate expressions in grouped queries.
Example error:
Using Greatest() and Count() together may cause:
OperationalError: Expression ... is not in GROUP BY
Solution: wrap non‑aggregate expressions with AnyValue
Book.objects.values(
greatest_pages=Greatest("pages", 600),
).annotate(
num_authors=Count("authors"),
pages_per_author=AnyValue(F("greatest_pages")) / F("num_authors"),
).aggregate(Avg("pages_per_author"))
Other databases detect functional dependencies automatically, so they don’t need AnyValue.
Conclusion
Django’s values() dramatically changes how annotations behave by introducing grouping. The order of annotate() and values() affects results, and order_by() can unintentionally alter grouping. Django also supports aggregating on annotations, handling empty groups, and resolving MySQL FULL_GROUP_BY issues using AnyValue. Mastering these concepts is essential for writing accurate and efficient analytical queries.