How to Use Group By in Django

As a Python developer, I’ve worked extensively with Django’s ORM to build scalable and efficient web applications. One of the most common tasks when working with databases is grouping data to extract meaningful insights. Django’s ORM makes this easy, but it can be tricky if you’re new to it.

In this article, I’ll walk you through how to perform Group By operations in Django. I’ll share practical examples relevant to real-world scenarios, with a particular focus on use cases common in the USA, such as grouping sales data by state or summarizing orders by customer.

Let’s get in!

What Is Group By in Django?

Grouping data means aggregating rows that share a common attribute. In SQL, this is done using the GROUP BY clause. Django abstracts this with its ORM using annotate() and values() methods.

For example, if you want to find the total sales per state, you group sales records by the state field and sum the sales amount for each state.

Read Outputting Python to HTML in Django

Method 1: Use values() and annotate() for Grouping

This is the most common method for performing a Group By operation in Django. You use values() to specify the fields to group by, and annotate() to apply aggregate functions like Sum(), Count(), or Avg().

Example: Grouping Sales by State

Imagine you have a model Sale with fields state and amount.

from django.db import models

class Sale(models.Model):
    state = models.CharField(max_length=50)
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    date = models.DateField()

To get total sales per state, you can write:

from django.db.models import Sum

sales_by_state = Sale.objects.values('state').annotate(total_sales=Sum('amount')).order_by('state')

for sale in sales_by_state:
    print(f"State: {sale['state']}, Total Sales: ${sale['total_sales']}")

You can see the output in the screenshot below.

How this works:

• values('state') groups the data by the state field.
• annotate(total_sales=Sum('amount')) computes the sum of amount for each group.
• order_by('state') sorts the results alphabetically by state.

Read Python Django Set Timezone

Method 2: Group with Multiple Fields

You can group by more than one field by passing multiple fields to values().

Example: Grouping Sales by State and Month

Let’s extend the previous example to group sales by both state and the month of the sale.

from django.db.models.functions import ExtractMonth

sales_by_state_month = (
    Sale.objects
    .annotate(month=ExtractMonth('date'))
    .values('state', 'month')
    .annotate(total_sales=Sum('amount'))
    .order_by('state', 'month')
)

for sale in sales_by_state_month:
    print(f"State: {sale['state']}, Month: {sale['month']}, Total Sales: ${sale['total_sales']}")

You can see the output in the screenshot below.

Explanation:

• ExtractMonth('date') extracts the month from the date field.
• We then group by both state and month.
• This allows you to see how sales vary across months for each state.

Check out If Condition In Django Template

Method 3: Use Count() to Group and Count Records

Sometimes, you want to know how many records belong to each group.

Example: Count Number of Sales per Customer

Suppose you have a Customer model and each sale is linked to a customer via a ForeignKey.

class Customer(models.Model):
    name = models.CharField(max_length=100)

class Sale(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    date = models.DateField()

To count how many sales each customer made:

from django.db.models import Count

sales_count = (
    Sale.objects
    .values('customer__name')
    .annotate(num_sales=Count('id'))
    .order_by('-num_sales')
)

for record in sales_count:
    print(f"Customer: {record['customer__name']}, Number of Sales: {record['num_sales']}")

You can see the output in the screenshot below.

Read Pyramid vs. Django

Method 4: Use F() Expressions with Group By

You can use F() expressions to perform calculations on fields during grouping.

Example: Calculate Average Sale per Customer

from django.db.models import Avg, F

average_sales = (
    Sale.objects
    .values('customer__name')
    .annotate(avg_sale=Avg(F('amount')))
    .order_by('customer__name')
)

for record in average_sales:
    print(f"Customer: {record['customer__name']}, Average Sale: ${record['avg_sale']:.2f}")

Check out Print Django Environment Variables

Method 5: Raw SQL Queries for Complex Grouping

Sometimes Django ORM might not be enough for very complex grouping scenarios. In such cases, raw SQL queries can be used.

Example: Grouping Sales by State Using Raw SQL

from django.db import connection

def get_sales_by_state():
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT state, SUM(amount) as total_sales
            FROM app_sale
            GROUP BY state
            ORDER BY state
        """)
        result = cursor.fetchall()
    return result

sales = get_sales_by_state()
for state, total in sales:
    print(f"State: {state}, Total Sales: ${total}")

Read Google Authentication in Django

Tips for Using Group By in Django

• Always use values() before annotate() to specify the grouping fields.
• Use order_by() to sort your grouped results.
• Use Django’s database functions like ExtractMonth, ExtractYear for date-based grouping.
• Remember that annotate() can be chained with multiple aggregate functions if needed.
• For large datasets, consider indexing the fields you are grouping by for better performance.

I hope this guide helps you master grouping data in Django ORM. Whether you’re working on sales reports, customer analytics, or any other data-driven application, these techniques will make your queries more efficient and your code cleaner.

If you have any questions or would like me to cover more advanced Django ORM topics, please feel free to reach out.

Other Python Django articles you may also like:

• Use Django Built-In Login System
• Build a To-Do List API in Django Rest Framework
• Create a Notes Taking app in Django

Bijay Kumar

I am Bijay Kumar, a Microsoft MVP in SharePoint. Apart from SharePoint, I started working on Python, Machine learning, and artificial intelligence for the last 5 years. During this time I got expertise in various Python libraries also like Tkinter, Pandas, NumPy, Turtle, Django, Matplotlib, Tensorflow, Scipy, Scikit-Learn, etc… for various clients in the United States, Canada, the United Kingdom, Australia, New Zealand, etc. Check out my profile.

enjoysharepoint.com/