The topic guide on Django’s database-abstraction API
described the way that you can use Django queries that create,
retrieve, update and delete individual objects. However, sometimes you will
need to retrieve values that are derived by summarizing or aggregating a
collection of objects. This topic guide describes the ways that aggregate values
can be generated and returned using Django queries.

Throughout this guide, we’ll refer to the following models. These models are
used to track the inventory for a series of online bookstores:

In a hurry? Here’s how to do common aggregate queries, assuming the models
above:

# Total number of books.>>>Book.objects.count()2452# Total number of books with publisher=BaloneyPress>>>Book.objects.filter(publisher__name='BaloneyPress').count()73# Average price across all books.>>>fromdjango.db.modelsimportAvg>>>Book.objects.all().aggregate(Avg('price')){'price__avg':34.35}# Max price across all books.>>>fromdjango.db.modelsimportMax>>>Book.objects.all().aggregate(Max('price')){'price__max':Decimal('81.20')}# Difference between the highest priced book and the average price of all books.>>>fromdjango.db.modelsimportFloatField>>>Book.objects.aggregate(...price_diff=Max('price',output_field=FloatField())-Avg('price')){'price_diff':46.85}# All the following queries involve traversing the Book<->Publisher# foreign key relationship backwards.# Each publisher, each with a count of books as a "num_books" attribute.>>>fromdjango.db.modelsimportCount>>>pubs=Publisher.objects.annotate(num_books=Count('book'))>>>pubs<QuerySet[<Publisher:BaloneyPress>,<Publisher:SalamiPress>,...]>>>>pubs[0].num_books73# Each publisher, with a separate count of books with a rating above and below 5>>>fromdjango.db.modelsimportQ>>>above_5=Count('book',filter=Q(book__rating__gt=5))>>>below_5=Count('book',filter=Q(book__rating__lte=5))>>>pubs=Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)>>>pubs[0].above_523>>>pubs[0].below_512# The top 5 publishers, in order by number of books.>>>pubs=Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]>>>pubs[0].num_books1323

Django provides two ways to generate aggregates. The first way is to generate
summary values over an entire QuerySet. For example, say you wanted to
calculate the average price of all books available for sale. Django’s query
syntax provides a means for describing the set of all books:

>>> Book.objects.all()

What we need is a way to calculate summary values over the objects that
belong to this QuerySet. This is done by appending an aggregate()
clause onto the QuerySet:

The all() is redundant in this example, so this could be simplified to:

>>> Book.objects.aggregate(Avg('price')){'price__avg': 34.35}

The argument to the aggregate() clause describes the aggregate value that
we want to compute - in this case, the average of the price field on the
Book model. A list of the aggregate functions that are available can be
found in the QuerySet reference.

aggregate() is a terminal clause for a QuerySet that, when invoked,
returns a dictionary of name-value pairs. The name is an identifier for the
aggregate value; the value is the computed aggregate. The name is
automatically generated from the name of the field and the aggregate function.
If you want to manually specify a name for the aggregate value, you can do so
by providing that name when you specify the aggregate clause:

The second way to generate summary values is to generate an independent
summary for each object in a QuerySet. For example, if you are
retrieving a list of books, you may want to know how many authors contributed
to each book. Each Book has a many-to-many relationship with the Author; we
want to summarize this relationship for each book in the QuerySet.

Per-object summaries can be generated using the
annotate() clause. When an annotate() clause is
specified, each object in the QuerySet will be annotated with the
specified values.

The syntax for these annotations is identical to that used for the
aggregate() clause. Each argument to annotate() describes
an aggregate that is to be calculated. For example, to annotate books with the
number of authors:

# Build an annotated queryset>>>fromdjango.db.modelsimportCount>>>q=Book.objects.annotate(Count('authors'))# Interrogate the first object in the queryset>>>q[0]<Book:TheDefinitiveGuidetoDjango>>>>q[0].authors__count2# Interrogate the second object in the queryset>>>q[1]<Book:PracticalDjangoProjects>>>>q[1].authors__count1

As with aggregate(), the name for the annotation is automatically derived
from the name of the aggregate function and the name of the field being
aggregated. You can override this default name by providing an alias when you
specify the annotation:

Unlike aggregate(), annotate() is not a terminal clause. The output
of the annotate() clause is a QuerySet; this QuerySet can be
modified using any other QuerySet operation, including filter(),
order_by(), or even additional calls to annotate().

So far, we have dealt with aggregates over fields that belong to the
model being queried. However, sometimes the value you want to aggregate
will belong to a model that is related to the model you are querying.

When specifying the field to be aggregated in an aggregate function, Django
will allow you to use the same double underscore notation that is used when referring to related fields in
filters. Django will then handle any table joins that are required to retrieve
and aggregate the related value.

For example, to find the price range of books offered in each store,
you could use the annotation:

This tells Django to retrieve the Store model, join (through the
many-to-many relationship) with the Book model, and aggregate on the
price field of the book model to produce a minimum and maximum value.

The same rules apply to the aggregate() clause. If you wanted to
know the lowest and highest price of any book that is available for sale
in any of the stores, you could use the aggregate:

In a way similar to Lookups that span relationships, aggregations and
annotations on fields of models or models that are related to the one you are
querying can include traversing “reverse” relationships. The lowercase name
of related models and double-underscores are used here too.

For example, we can ask for all publishers, annotated with their respective
total book stock counters (note how we use 'book' to specify the
Publisher -> Book reverse foreign key hop):

(Every Publisher in the resulting QuerySet will have an extra attribute
called book__count.)

We can also ask for the oldest book of any of those managed by every publisher:

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

(The resulting dictionary will have a key called 'oldest_pubdate'. If no
such alias were specified, it would be the rather long 'book__pubdate__min'.)

This doesn’t apply just to foreign keys. It also works with many-to-many
relations. For example, we can ask for every author, annotated with the total
number of pages considering all the books the author has (co-)authored (note how we
use 'book' to specify the Author -> Book reverse many-to-many hop):

>>> Author.objects.annotate(total_pages=Sum('book__pages'))

(Every Author in the resulting QuerySet will have an extra attribute
called total_pages. If no such alias were specified, it would be the rather
long book__pages__sum.)

Or ask for the average rating of all the books written by author(s) we have on
file:

>>> Author.objects.aggregate(average_rating=Avg('book__rating'))

(The resulting dictionary will have a key called 'average_rating'. If no
such alias were specified, it would be the rather long 'book__rating__avg'.)

Aggregates can also participate in filters. Any filter() (or
exclude()) applied to normal model fields will have the effect of
constraining the objects that are considered for aggregation.

When used with an annotate() clause, a filter has the effect of
constraining the objects for which an annotation is calculated. For example,
you can generate an annotated list of all books that have a title starting
with “Django” using the query:

When used with an aggregate() clause, a filter has the effect of
constraining the objects over which the aggregate is calculated.
For example, you can generate the average price of all books with a
title that starts with “Django” using the query:

Each Author in the result set will have the num_books and
highly_rated_books attributes.

Choosing between filter and QuerySet.filter()

Avoid using the filter argument with a single annotation or
aggregation. It’s more efficient to use QuerySet.filter() to exclude
rows. The aggregation filter argument is only useful when using two or
more aggregations over the same relations with different conditionals.

When developing a complex query that involves both annotate() and
filter() clauses, pay particular attention to the order in which the
clauses are applied to the QuerySet.

When an annotate() clause is applied to a query, the annotation is computed
over the state of the query up to the point where the annotation is requested.
The practical implication of this is that filter() and annotate() are
not commutative operations.

Both queries return a list of publishers that have at least one book with a
rating exceeding 3.0, hence publisher C is excluded.

In the first query, the annotation precedes the filter, so the filter has no
effect on the annotation. distinct=True is required to avoid a query
bug.

The second query counts the number of books that have a rating exceeding 3.0
for each publisher. The filter precedes the annotation, so the filter
constrains the objects considered when calculating the annotation.

The first query asks for the average rating of all a publisher’s books for
publisher’s that have at least one book with a rating exceeding 3.0. The second
query asks for the average of a publisher’s book’s ratings for only those
ratings exceeding 3.0.

It’s difficult to intuit how the ORM will translate complex querysets into SQL
queries so when in doubt, inspect the SQL with str(queryset.query) and
write plenty of tests.

Ordinarily, annotations are generated on a per-object basis - an annotated
QuerySet will return one result for each object in the original
QuerySet. However, when a values() clause is used to constrain the
columns that are returned in the result set, the method for evaluating
annotations is slightly different. Instead of returning an annotated result
for each result in the original QuerySet, the original results are
grouped according to the unique combinations of the fields specified in the
values() clause. An annotation is then provided for each unique group;
the annotation is computed over all members of the group.

For example, consider an author query that attempts to find out the average
rating of books written by each author:

>>> Author.objects.annotate(average_rating=Avg('book__rating'))

This will return one result for each author in the database, annotated with
their average book rating.

However, the result will be slightly different if you use a values() clause:

In this example, the authors will be grouped by name, so you will only get
an annotated result for each unique author name. This means if you have
two authors with the same name, their results will be merged into a single
result in the output of the query; the average will be computed as the
average over the books written by both authors.

As with the filter() clause, the order in which annotate() and
values() clauses are applied to a query is significant. If the
values() clause precedes the annotate(), the annotation will be
computed using the grouping described by the values() clause.

However, if the annotate() clause precedes the values() clause,
the annotations will be generated over the entire query set. In this case,
the values() clause only constrains the fields that are generated on
output.

For example, if we reverse the order of the values() and annotate()
clause from our previous example:

This will now yield one unique result for each author; however, only
the author’s name and the average_rating annotation will be returned
in the output data.

You should also note that average_rating has been explicitly included
in the list of values to be returned. This is required because of the
ordering of the values() and annotate() clause.

If the values() clause precedes the annotate() clause, any annotations
will be automatically added to the result set. However, if the values()
clause is applied after the annotate() clause, you need to explicitly
include the aggregate column.

Fields that are mentioned in the order_by() part of a queryset (or which
are used in the default ordering on a model) are used when selecting the
output data, even if they are not otherwise specified in the values()
call. These extra fields are used to group “like” results together and they
can make otherwise identical result rows appear to be separate. This shows up,
particularly, when counting things.

…which will group the Item objects by their common data values and
then count the number of id values in each group. Except that it won’t
quite work. The default ordering by name will also play a part in the
grouping, so this query will group by distinct (data,name) pairs, which
isn’t what you want. Instead, you should construct this queryset:

Item.objects.values("data").annotate(Count("id")).order_by()

…clearing any ordering in the query. You could also order by, say, data
without any harmful effects, since that is already playing a role in the
query.

This behavior is the same as that noted in the queryset documentation for
distinct() and the general rule is the
same: normally you won’t want extra columns playing a part in the result, so
clear out the ordering, or at least make sure it’s restricted only to those
fields you also select in a values() call.

Note

You might reasonably ask why Django doesn’t remove the extraneous columns
for you. The main reason is consistency with distinct() and other
places: Django never removes ordering constraints that you have
specified (and we can’t change those other methods’ behavior, as that
would violate our API stability policy).

You can also generate an aggregate on the result of an annotation. When you
define an aggregate() clause, the aggregates you provide can reference
any alias defined as part of an annotate() clause in the query.

For example, if you wanted to calculate the average number of authors per
book you first annotate the set of books with the author count, then
aggregate that author count, referencing the annotation field: