Aggregate and annotate methods in Django

Introduction

Aggregate and annotate methods help you get summary information from your database easily. They let you count, sum, or find averages without writing complex code.

You want to count how many items are in a list stored in the database.

You need to find the total price of all orders made by a customer.

You want to add extra information to each item, like the number of comments it has.

You want to find the average rating of products in a store.

Syntax

Django

from django.db.models import Count, Sum, Avg

# Aggregate example
Model.objects.aggregate(Sum('field_name'))

# Annotate example
Model.objects.annotate(new_field=Count('related_field'))

aggregate() returns a dictionary with the summary result.

annotate() adds new fields to each item in the query result.

Examples

This counts all comments related to posts and returns a dictionary with the total.

Django

from django.db.models import Count

# Count total comments
Post.objects.aggregate(total_comments=Count('comments'))

This sums the price field of all orders and returns the total price.

Django

from django.db.models import Sum

# Sum prices of all orders
Order.objects.aggregate(total_price=Sum('price'))

This adds a new field comment_count to each post showing how many comments it has.

Django

from django.db.models import Count

# Add comment count to each post
Post.objects.annotate(comment_count=Count('comments'))

Sample Program

This example shows how to add two new fields to each author: the number of books they wrote and the total price of those books. It prints this info for each author.

Django

from django.db import models
from django.db.models import Count, Sum

# Define simple models
class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, related_name='books', on_delete=models.CASCADE)
    price = models.DecimalField(max_digits=6, decimal_places=2)

# Sample usage
# Count how many books each author has and sum their prices
authors = Author.objects.annotate(book_count=Count('books'), total_price=Sum('books__price'))

for author in authors:
    print(f"{author.name} has {author.book_count} books costing a total of ${author.total_price}")

Important Notes

Use aggregate() when you want one summary result for the whole query.

Use annotate() when you want to add summary info to each item in the query.

Remember to import the right functions like Count, Sum, or Avg from django.db.models.

Summary

aggregate() gives you overall summary data as a dictionary.

annotate() adds calculated fields to each object in your query.

They help you get useful numbers like counts, sums, and averages easily.