6.14. ORM Aggregate

6.14.1. Count

Total number of books:

Book.objects.count()
# 2452

Total number of books with publisher=BaloneyPress:

Book.objects.filter(publisher__name='BaloneyPress').count()
# 73

6.14.2. Average

Average price across all books:

from django.db.models import Avg

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

6.14.3. Maximum

Max price across all books:

from django.db.models import Max

Book.objects.all().aggregate(Max('price'))
# {'price__max': Decimal('81.20')}

6.14.4. Average, Maximum, Minimum

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

Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
# {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

6.14.5. Output

Difference between the highest priced book and the average price of all books:

from django.db.models import FloatField

Book.objects.aggregate(price_diff=Max('price', output_field=FloatField()) - Avg('price'))
# {'price_diff': 46.85}

6.14.6. Annotate

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:

from django.db.models import Count

pubs = Publisher.objects.annotate(num_books=Count('book'))
# <QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>

pubs[0].num_books
# 73

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_books
# 1323