Django aggregation and annotation
last modified November 13, 2026 Python 3.15
In this article we summarize data with Django's aggregation tools. We compute
totals and averages over a whole QuerySet with aggregate(), attach a
computed value to every object with annotate(), group rows with
values(), filter on aggregates, count conditionally, and compute with
F expressions and database functions. The examples use Django 6.1,
Python 3.15, and the default SQLite database.
Aggregation and annotation
An aggregate function turns many rows into one value: a count, a sum, an
average, a minimum, or a maximum. Django offers them in
django.db.models as Count, Sum,
Avg, Min, and Max. There are two ways to use
them. The aggregate() method is a terminal operation: it summarizes the
whole QuerySet and returns a dictionary. The annotate() method
adds the computed value to each object of a QuerySet as an attribute, and it returns
a QuerySet again, so the call can be followed by filter(),
order_by(), and the other methods. When the annotated expression is an aggregate,
the ORM emits a GROUP BY clause. The models and queries are
described in Django models and
Django QuerySets.
The example project
The project is created with uv; the steps are explained in
Django introduction. The commands below create a project
directory, install Django, generate the project with the configuration package
config, start the app shop, and create the folder demos for demo scripts.
$ mkdir aggregation $ cd aggregation $ uv init --bare $ uv add django $ uv run django-admin startproject config . $ uv run python manage.py startapp shop $ mkdir demos
The trailing dot in startproject config . creates manage.py and the
config package in the current directory instead of one level deeper. A new app is
ignored by Django until it is listed in INSTALLED_APPS, so we add
the app to the list in config/settings.py; the other
entries are generated by startproject.
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'shop',
]
The listings in this article are of two kinds. A listing with a solid blue border and the label
Project file is a file of the application: models, views, templates, tests, and
settings. A listing with a dashed amber border and the label Demo script is a small
helper in the demos folder; it sets up Django, runs a few lines of code, and prints
the results, so that we can watch the behaviour. Demo scripts are not part of the application;
we run one from the project directory with uv run python -m demos.name.
The app has
authors and their books. The related_name='books' of the foreign key lets
us follow the relation from an author to the books.
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=100)
country = models.CharField(max_length=50)
class Meta:
ordering = ['name']
def __str__(self):
return self.name
class Book(models.Model):
title = models.CharField(max_length=200)
author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
year = models.PositiveSmallIntegerField()
pages = models.PositiveIntegerField()
price = models.DecimalField(max_digits=6, decimal_places=2)
rating = models.FloatField(null=True, blank=True)
sold = models.PositiveIntegerField(default=0)
class Meta:
ordering = ['title']
def __str__(self):
return self.title
A book has a year, a number of pages, a price, an optional rating, and the number of sold
copies. A script fills the tables with six authors and eight books; one author,
Karel Capek, has no books, and one book has no rating. These gaps show how
aggregates treat missing data.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from decimal import Decimal
from shop.models import Author, Book
authors = {
name: Author.objects.create(name=name, country=country)
for name, country in [
('George Orwell', 'UK'), ('Jane Austen', 'UK'), ('Frank Herbert', 'US'),
('Ursula K. Le Guin', 'US'), ('Stanislaw Lem', 'Poland'), ('Karel Capek', 'Czechia'),
]
}
rows = [
('Nineteen Eighty-Four', 'George Orwell', 1949, 328, '9.99', 4.6, 12),
('Animal Farm', 'George Orwell', 1945, 112, '6.50', 4.3, 8),
('Emma', 'Jane Austen', 1815, 474, '7.50', 4.0, 1),
('Pride and Prejudice', 'Jane Austen', 1813, 432, '8.25', 4.7, 15),
('Dune', 'Frank Herbert', 1965, 612, '12.90', 4.5, 9),
('The Dispossessed', 'Ursula K. Le Guin', 1974, 387, '11.00', 4.4, 2),
('Solaris', 'Stanislaw Lem', 1961, 204, '10.50', 4.2, 3),
('Fiasco', 'Stanislaw Lem', 1986, 320, '10.00', None, 0),
]
for title, author, year, pages, price, rating, sold in rows:
Book.objects.create(title=title, author=authors[author], year=year, pages=pages,
price=Decimal(price), rating=rating, sold=sold)
print(Author.objects.count(), 'authors,', Book.objects.count(), 'books')
The script creates the authors and keeps them in a dictionary, and then creates the books
with the authors as foreign keys. The tables are created with makemigrations and
migrate (see Django migrations).
$ uv run python manage.py makemigrations shop
Migrations for 'shop':
shop/migrations/0001_initial.py
+ Create model Author
+ Create model Book
$ uv run python manage.py migrate shop
Operations to perform:
Apply all migrations: shop
Running migrations:
Applying shop.0001_initial... OK
$ uv run python -m demos.seed
6 authors, 8 books
aggregate()
The aggregate() method takes aggregate expressions and returns a
dictionary. A positional argument gets an automatic key made of the field and the
function, such as price__max; a keyword argument gives the result
its name. The method works on the current QuerySet, so a preceding
filter() limits the rows that are summarized.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from decimal import Decimal
from django.db.models import Avg, Count, Max, Min, Sum
from shop.models import Book
print(Book.objects.aggregate(Count('id'), Avg('pages'), Max('price')))
print(Book.objects.aggregate(total=Count('id'), cheapest=Min('price'), pages=Sum('pages')))
prices = Book.objects.aggregate(low=Min('price'), high=Max('price'), mean=Avg('price'))
print({name: value.quantize(Decimal('0.01')) for name, value in prices.items()})
print(Book.objects.filter(author__country='UK').aggregate(sold=Sum('sold')))
print(Book.objects.filter(year__gt=2000).aggregate(sold=Sum('sold'), average=Avg('price')))
print(Book.objects.aggregate(rated=Count('rating'), all=Count('*')))
The first call uses the automatic keys. The second one names the results.
The third one converts the prices to two decimal places: SQLite has no decimal type
and calculates with floating-point numbers, so Max('price') comes back as
12.9000000000000 in the first line, and the digits after the cents are noise. We round in Python with
quantize(); PostgreSQL returns exact decimals. The fourth call
summarizes only the UK books. The fifth one filters out all rows, and
Sum and Avg of an empty set are None, not zero.
The last line shows that Count('rating') counts only the rows with a
value, whereas Count('*') counts all rows.
$ uv run python -m demos.aggregate
{'id__count': 8, 'pages__avg': 358.625, 'price__max': Decimal('12.9000000000000')}
{'total': 8, 'cheapest': Decimal('6.5'), 'pages': 2869}
{'low': Decimal('6.50'), 'high': Decimal('12.90'), 'mean': Decimal('9.58')}
{'sold': 36}
{'sold': None, 'average': None}
{'rated': 7, 'all': 8}
annotate()
With annotate() each object gets the computed value as an
attribute. When the expression spans a relation, such as
Count('books') on authors, Django joins the related table and groups by
the author columns, so we get one object per author, each with its own count.
An annotation can be used like a field in filter() and
order_by().
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db.models import Avg, Count, Sum
from shop.models import Author
authors = Author.objects.annotate(book_count=Count('books'), sold=Sum('books__sold'))
for author in authors:
print(f'{author.name:20} {author.book_count} {author.sold}')
print(authors.query)
print('---')
best = Author.objects.annotate(average=Avg('books__rating')).order_by('-average')[:2]
print([(a.name, round(a.average, 2)) for a in best])
print(list(Author.objects.annotate(n=Count('books')).filter(n__gt=1)))
print(list(Author.objects.annotate(n=Count('books')).filter(n=0)))
The first part annotates each author with the number of books and the number of sold
copies. The author without books appears with a count of zero and a sum of
None, because the LEFT OUTER JOIN keeps the row; printing
authors.query shows the JOIN and the GROUP BY.
The second part averages the ratings and orders by the average. It selects the
authors with more than one book and, with n=0, the authors without books.
$ uv run python -m demos.annotate
George Orwell 2 20
Jane Austen 2 16
Frank Herbert 1 9
Ursula K. Le Guin 1 2
Stanislaw Lem 2 3
Karel Capek 0 None
SELECT "shop_author"."id", "shop_author"."name", "shop_author"."country", COUNT("shop_book"."id") AS "book_count", SUM("shop_book"."sold") AS "sold" FROM "shop_author" LEFT OUTER JOIN "shop_book" ON ("shop_author"."id" = "shop_book"."author_id") GROUP BY "shop_author"."id", "shop_author"."name", "shop_author"."country"
---
[('Frank Herbert', 4.5), ('George Orwell', 4.45)]
[<Author: George Orwell>, <Author: Jane Austen>, <Author: Stanislaw Lem>]
[<Author: Karel Capek>]
Grouping with values()
To group by a column that is not a model, we call values()
before annotate(). The fields in values() become the
GROUP BY columns, and the annotations are computed for each group. The
result is a QuerySet of dictionaries. The order of the calls matters: with
annotate() first, the aggregate would be computed for each object, and
values() after it would only select columns.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db.models import Avg, Count, Sum
from shop.models import Book
groups = (Book.objects.values('author__country')
.annotate(books=Count('id'), pages=Sum('pages'), price=Avg('price'))
.order_by('author__country'))
for row in groups:
print(row['author__country'], row['books'], row['pages'], round(row['price'], 2))
print(groups.query)
print('---')
years = (Book.objects.values('year').annotate(books=Count('id'))
.order_by('-books', 'year')[:3])
print(list(years))
The first query groups the books by the country of the author and
computes the number of books, the pages, and the average price. The SQL shows that the
group is a single column. The explicit order_by() is needed to get a
stable order of the groups; the Meta.ordering of the model is ignored in a
GROUP BY query. The second query groups by year and takes the three most
common years; the ties are broken by the year.
$ uv run python -m demos.groupby
Poland 2 524 10.25
UK 4 1346 8.06
US 2 999 11.95
SELECT "shop_author"."country" AS "author__country", COUNT("shop_book"."id") AS "books", SUM("shop_book"."pages") AS "pages", (CAST(AVG("shop_book"."price") AS NUMERIC)) AS "price" FROM "shop_book" INNER JOIN "shop_author" ON ("shop_book"."author_id" = "shop_author"."id") GROUP BY 1 ORDER BY 1 ASC
---
[{'year': 1813, 'books': 1}, {'year': 1815, 'books': 1}, {'year': 1945, 'books': 1}]
Filtering on aggregates
The place of the filter() call matters. A filter that comes
before annotate() restricts the rows that are aggregated and becomes a
WHERE clause. A filter on the annotation that comes after it
restricts the groups and becomes a HAVING clause. There is one trap:
filtering after annotate() on a different multi-valued relation adds a second
join, and the rows are counted again for each joined row.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db.models import Count
from shop.models import Author
before = Author.objects.filter(books__year__gte=1950).annotate(n=Count('books'))
after = Author.objects.annotate(n=Count('books')).filter(books__year__gte=1950)
print([(a.name, a.n) for a in before])
print([(a.name, a.n) for a in after])
print('---')
having = Author.objects.annotate(n=Count('books')).filter(n__gte=2)
print(having.query)
print([a.name for a in having])
print('---')
fixed = Author.objects.annotate(n=Count('books', distinct=True)).filter(books__year__gte=1950)
print([(a.name, a.n) for a in fixed])
In the first query the filter comes first, so only the books from 1950 on are counted: Stanislaw
Lem has two such books. In the second query the filter comes after
annotate() and uses the same relation. Django joins the book table a second
time, so the 2 books are joined with 2 books, and the count is 4. The output of the third query shows
the HAVING clause, and the last query fixes the problem with
Count('books', distinct=True), which counts every book once.
$ uv run python -m demos.order
[('Frank Herbert', 1), ('Ursula K. Le Guin', 1), ('Stanislaw Lem', 2)]
[('Frank Herbert', 1), ('Ursula K. Le Guin', 1), ('Stanislaw Lem', 4)]
---
SELECT "shop_author"."id", "shop_author"."name", "shop_author"."country", COUNT("shop_book"."id") AS "n" FROM "shop_author" LEFT OUTER JOIN "shop_book" ON ("shop_author"."id" = "shop_book"."author_id") GROUP BY "shop_author"."id", "shop_author"."name", "shop_author"."country" HAVING COUNT("shop_book"."id") >= 2
['George Orwell', 'Jane Austen', 'Stanislaw Lem']
---
[('Frank Herbert', 1), ('Ursula K. Le Guin', 1), ('Stanislaw Lem', 2)]
Conditional aggregation
Aggregates accept a filter argument with a Q object, which
makes the function consider only the matching rows. It generates SQL such as
COUNT(...) FILTER (WHERE ...) or an equivalent CASE expression and
lets us compute several conditional counts in one query. For labels and
categories there are the Case and When expressions: they work like
if/elif/else and can be used in annotate(),
order_by(), and inside aggregates.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db.models import Case, Count, F, Q, Sum, Value, When
from django.db.models import CharField
from shop.models import Author, Book
authors = Author.objects.annotate(
old=Count('books', filter=Q(books__year__lt=1900)),
modern=Count('books', filter=Q(books__year__gte=1900)),
)
for author in authors:
print(f'{author.name:20} old={author.old} modern={author.modern}')
print('---')
books = Book.objects.annotate(
size=Case(
When(pages__lt=250, then=Value('short')),
When(pages__lt=450, then=Value('medium')),
default=Value('long'),
output_field=CharField(),
)
).order_by('pages')
for book in books:
print(f'{book.title:22} {book.pages:4} {book.size}')
print('---')
sizes = books.values('size').annotate(n=Count('id')).order_by('size')
print(list(sizes))
print(Book.objects.aggregate(
long=Count('id', filter=Q(pages__gte=450)),
sold_old=Sum('sold', filter=Q(year__lt=1950)),
))
The first part counts for each author the books written before and after 1900 in one
query. The second one labels each book as short, medium, or long with Case;
the output_field tells Django the type of the result. The third part groups by
the label and counts, and the last query computes two conditional aggregates over the
whole table: the number of long books and the number of copies sold from the books
published before 1950.
$ uv run python -m demos.conditional
George Orwell old=0 modern=2
Jane Austen old=2 modern=0
Frank Herbert old=0 modern=1
Ursula K. Le Guin old=0 modern=1
Stanislaw Lem old=0 modern=2
Karel Capek old=0 modern=0
---
Animal Farm 112 short
Solaris 204 short
Fiasco 320 medium
Nineteen Eighty-Four 328 medium
The Dispossessed 387 medium
Pride and Prejudice 432 medium
Emma 474 long
Dune 612 long
---
[{'size': 'long', 'n': 2}, {'size': 'medium', 'n': 4}, {'size': 'short', 'n': 2}]
{'long': 2, 'sold_old': 36}
Expressions and functions
Aggregates can be applied to expressions, not only to fields. An
F expression refers to a column, and arithmetic on F objects is performed by the
database. When the operands have different types, such as a decimal price and an
integer count, Django cannot tell the type of the result, and we state it with
ExpressionWrapper and output_field. The module
django.db.models.functions has database functions such as
Coalesce, Round, Upper, and Greatest.
The Coalesce function returns its first non-null argument, which is the usual
way to replace the None of an empty aggregate.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from decimal import Decimal
from django.db.models import Avg, DecimalField, ExpressionWrapper, F, Sum
from django.db.models.functions import Coalesce, Round, Upper
from shop.models import Author, Book
revenue = ExpressionWrapper(F('price') * F('sold'), output_field=DecimalField(max_digits=10, decimal_places=2))
books = Book.objects.annotate(revenue=revenue).order_by('-revenue')[:3]
for book in books:
print(f'{book.title:22} {book.revenue:.2f}')
print(round(Book.objects.aggregate(total=Sum(revenue))['total'], 2))
print('---')
print(Book.objects.aggregate(avg=Avg('rating')))
print(Book.objects.aggregate(avg=Coalesce(Avg('rating'), 0.0))['avg'])
print(Author.objects.filter(country='Czechia').aggregate(avg=Avg('books__rating')))
print(Author.objects.filter(country='Czechia').aggregate(avg=Coalesce(Avg('books__rating'), 0.0)))
print('---')
rows = Author.objects.annotate(
shout=Upper('name'),
mean=Round(Avg('books__rating'), 2),
).order_by('name')[:3]
print([(a.shout, a.mean) for a in rows])
The script annotates each book with its revenue, the price multiplied by the sold
copies, lists the three best sellers, and sums the revenue of all books in a single aggregate.
It then compares the average rating with and without Coalesce: for the Czech author without books the
plain average is None, whereas Coalesce(Avg(...), 0.0) returns
0.0. The last query combines Upper with
Round in annotations.
$ uv run python -m demos.expressions
Pride and Prejudice 123.75
Nineteen Eighty-Four 119.88
Dune 116.10
472.73
---
{'avg': 4.385714285714285}
4.385714285714285
{'avg': None}
{'avg': 0.0}
---
[('FRANK HERBERT', 4.5), ('GEORGE ORWELL', 4.45), ('JANE AUSTEN', 4.35)]
Testing aggregates
The tests create three authors and three books, and check the dictionary returned
by aggregate(), the attributes added by annotate(), the
HAVING filter, the grouping by country, and the conditional count.
from decimal import Decimal
from django.db.models import Avg, Count, Q, Sum
from django.test import TestCase
from .models import Author, Book
class AggregationTests(TestCase):
@classmethod
def setUpTestData(cls):
orwell = Author.objects.create(name='George Orwell', country='UK')
lem = Author.objects.create(name='Stanislaw Lem', country='Poland')
Author.objects.create(name='Karel Capek', country='Czechia')
for title, author, year, pages, price, sold in [
('Animal Farm', orwell, 1945, 112, '6.50', 8),
('Nineteen Eighty-Four', orwell, 1949, 328, '9.99', 12),
('Solaris', lem, 1961, 204, '10.50', 3),
]:
Book.objects.create(title=title, author=author, year=year, pages=pages,
price=Decimal(price), sold=sold)
def test_aggregate_returns_a_dict(self):
result = Book.objects.aggregate(total=Count('id'), pages=Sum('pages'))
self.assertEqual(result, {'total': 3, 'pages': 644})
def test_aggregate_on_empty_set_gives_none(self):
self.assertIsNone(Book.objects.filter(year__gt=2000).aggregate(Avg('price'))['price__avg'])
def test_annotate_adds_an_attribute(self):
authors = Author.objects.annotate(n=Count('books'))
self.assertEqual({a.name: a.n for a in authors},
{'Karel Capek': 0, 'George Orwell': 2, 'Stanislaw Lem': 1})
def test_filter_after_annotate_is_having(self):
names = [a.name for a in Author.objects.annotate(n=Count('books')).filter(n__gte=2)]
self.assertEqual(names, ['George Orwell'])
def test_values_annotate_groups(self):
rows = Book.objects.values('author__country').annotate(n=Count('id')).order_by('author__country')
self.assertEqual(list(rows), [{'author__country': 'Poland', 'n': 1},
{'author__country': 'UK', 'n': 2}])
def test_conditional_count(self):
result = Book.objects.aggregate(modern=Count('id', filter=Q(year__gte=1950)))
self.assertEqual(result['modern'], 1)
The test test_aggregate_on_empty_set_gives_none documents the behavior
that surprises most newcomers: an aggregate over no rows is None. Each test
runs in a transaction that is rolled back, so the tests are independent.
$ uv run python manage.py test Found 6 test(s). System check identified no issues (0 silenced). Creating test database for alias 'default'... ...... ---------------------------------------------------------------------- Ran 6 tests in 0.004s OK Destroying test database for alias 'default'...
Source
Django documentation: Aggregation
Django documentation: Aggregation functions
Django documentation: Conditional expressions
Django documentation: Database functions
In this article we have summarized data with aggregate(), annotated
objects with annotate(), grouped rows with values(), filtered on
aggregates, and used conditional expressions and database functions. The basics of
querying are in Django QuerySets, and the
data is defined by Django models. The next step is
to make these queries fast, as described in
Django query performance.
Author
List all Django tutorials.