Django QuerySet Aggregate

Key Aggregate Functions

from django.db.models import Sum
total = Book.objects.aggregate(
    Sum('price')
)

from django.db.models import Avg
avg = Book.objects.aggregate(
    Avg('rating')
)

from django.db.models import Count
count = Book.objects.aggregate(
    Count('id')
)

from django.db.models import Min, Max
Book.objects.aggregate(
    Min('price'), Max('price')
)

🔹 Using Sum()

The Sum() function calculates the total of all values in a field. This is useful for finding totals like revenue, inventory quantities, or cumulative scores.

# models.py
from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=200)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    pages = models.IntegerField()
    copies_sold = models.IntegerField()

# views.py
from django.db.models import Sum
from myapp.models import Book

# Calculate total value of all books
total_value = Book.objects.aggregate(Sum('price'))
print(f"Total: ${total_value['price__sum']}")

# Calculate total pages
total_pages = Book.objects.aggregate(Sum('pages'))

# Calculate total copies sold
total_sold = Book.objects.aggregate(Sum('copies_sold'))
print(f"Copies sold: {total_sold['copies_sold__sum']}")

🔹 Using Avg()

The Avg() function calculates the average (mean) value of a field. Perfect for finding average prices, ratings, or any numeric metric across your dataset.

from django.db.models import Avg
from myapp.models import Book

# Calculate average price
avg_price = Book.objects.aggregate(Avg('price'))
print(f"Average price: ${avg_price['price__avg']:.2f}")

# Calculate average rating
avg_rating = Book.objects.aggregate(Avg('rating'))
print(f"Average rating: {avg_rating['rating__avg']:.1f}")

# Calculate average pages
avg_pages = Book.objects.aggregate(Avg('pages'))

# Average for filtered QuerySet
published_avg = Book.objects.filter(
    published=True
).aggregate(Avg('price'))
print(f"Published books avg: ${published_avg['price__avg']:.2f}")

🔹 Using Count()

The Count() function counts the number of objects or non-null values in a field. Use it to get totals, track records, or count specific conditions.

from django.db.models import Count
from myapp.models import Book

# Count all books
total_books = Book.objects.aggregate(Count('id'))
print(f"Total books: {total_books['id__count']}")

# Count published books
published_count = Book.objects.filter(
    published=True
).aggregate(Count('id'))

# Count books with ratings
rated_books = Book.objects.aggregate(Count('rating'))

# Multiple counts
stats = Book.objects.aggregate(
    total=Count('id'),
    published=Count('id', filter=Q(published=True))
)
print(f"Total: {stats['total']}, Published: {stats['published']}")

🔹 Using Min() and Max()

The Min() and Max() functions find the smallest and largest values in a field. Essential for finding price ranges, date boundaries, or extreme values in your data.

from django.db.models import Min, Max
from myapp.models import Book

# Find cheapest book
cheapest = Book.objects.aggregate(Min('price'))
print(f"Cheapest: ${cheapest['price__min']}")

# Find most expensive book
most_expensive = Book.objects.aggregate(Max('price'))
print(f"Most expensive: ${most_expensive['price__max']}")

# Get both at once
price_range = Book.objects.aggregate(
    min_price=Min('price'),
    max_price=Max('price')
)
print(f"Price range: ${price_range['min_price']} - ${price_range['max_price']}")

# Find oldest and newest publication dates
date_range = Book.objects.aggregate(
    oldest=Min('published_date'),
    newest=Max('published_date')
)

🔹 Multiple Aggregations

You can calculate multiple aggregate values in a single query by passing multiple functions to aggregate(). This is more efficient than making separate database calls.

from django.db.models import Sum, Avg, Count, Min, Max
from myapp.models import Book

# Multiple aggregations at once
stats = Book.objects.aggregate(
    total_books=Count('id'),
    total_value=Sum('price'),
    avg_price=Avg('price'),
    cheapest=Min('price'),
    most_expensive=Max('price'),
    avg_rating=Avg('rating')
)

print(f"Total Books: {stats['total_books']}")
print(f"Total Value: ${stats['total_value']}")
print(f"Average Price: ${stats['avg_price']:.2f}")
print(f"Price Range: ${stats['cheapest']} - ${stats['most_expensive']}")
print(f"Average Rating: {stats['avg_rating']:.1f}")

🔹 Aggregate with Filters

Combine aggregate() with filter() to calculate statistics for specific subsets of your data. This lets you compare different groups or analyze specific conditions.

from django.db.models import Avg, Count
from myapp.models import Book

# Average price of published books only
published_avg = Book.objects.filter(
    published=True
).aggregate(Avg('price'))

# Count expensive books
expensive_count = Book.objects.filter(
    price__gt=50
).aggregate(Count('id'))

# Average rating of affordable books
affordable_rating = Book.objects.filter(
    price__lt=30
).aggregate(Avg('rating'))

print(f"Published avg: ${published_avg['price__avg']:.2f}")
print(f"Expensive books: {expensive_count['id__count']}")
print(f"Affordable rating: {affordable_rating['rating__avg']:.1f}")