Aggregate and annotate methods in Django - Mini Project: Build & Apply

Using Aggregate and Annotate Methods in Django ORM

📖 Scenario: You are building a simple Django app to manage a bookstore. You have a model called Book with fields for title, author, and price. You want to learn how to use Django's ORM aggregate and annotate methods to calculate summary data and add extra information to querysets.

🎯 Goal: Learn to use Django ORM's aggregate method to find the total price of all books and the annotate method to add the count of books per author.

📋 What You'll Learn

Create a Django model Book with fields title (string), author (string), and price (decimal).

Use Django ORM's aggregate method to calculate the total price of all books.

Use Django ORM's annotate method to add a count of books for each author.

Write queries using exact variable names and methods as instructed.

💡 Why This Matters

🌍 Real World

In real bookstore or inventory apps, you often need to calculate totals and group data by categories like author or genre. Django's aggregate and annotate methods help you do this efficiently in the database.

💼 Career

Understanding Django ORM's aggregate and annotate methods is essential for backend developers working with Django. It helps in writing efficient queries for reports, dashboards, and data summaries.

1

Create the Book model with fields

Create a Django model class called Book with fields title as CharField(max_length=100), author as CharField(max_length=100), and price as DecimalField(max_digits=6, decimal_places=2).

Django

# Define the Book model with title, author, and price fields
# Your code here

Need a hint?

Use Django's models.Model as the base class. Define each field with the exact names and types.

2

Create a queryset variable for all books

Create a variable called all_books that holds the queryset of all Book objects using Book.objects.all().

Django

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=6, decimal_places=2)

# Create a queryset variable all_books with all Book objects
# Your code here

Need a hint?

Use Book.objects.all() to get all book records and assign it to all_books.

3

Use aggregate to calculate total price of all books

Create a variable called total_price that uses all_books.aggregate() with models.Sum('price') to calculate the total price of all books. Use the key 'total' in the aggregate dictionary.

Django

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=6, decimal_places=2)

all_books = Book.objects.all()

# Calculate total_price using aggregate and Sum on price
# Your code here

Need a hint?

Use aggregate(total=models.Sum('price')) on the queryset to get the sum of prices with key total.

4

Use annotate to add book count per author

Create a variable called books_per_author that uses Book.objects.values('author').annotate() with count=models.Count('id') to add the count of books for each author.

Django

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=6, decimal_places=2)

all_books = Book.objects.all()

total_price = all_books.aggregate(total=models.Sum('price'))

# Create books_per_author using values('author') and annotate with count=models.Count('id')
# Your code here

Need a hint?

Use values('author') to group by author, then annotate(count=models.Count('id')) to add book counts.