How to Query as GROUP BY in Django?

Last Updated : 04 Jul, 2024

In Django, the powerful ORM (Object-Relational Mapping) allows developers to interact with databases using Python code. One common database operation is the GROUP BY query, which groups rows sharing a property so that aggregate functions can be applied to each group. This article will guide you through creating a small Django project that demonstrates how to perform GROUP BY queries.

How to Query as GROUP BY in Django?

We'll create a Django project named myproject and an app called myapp. The project will display data grouped by a specific field, mimicking a GROUP BY query in SQL.

Step 1: Setting Up the Django Project

First, ensure you have Django installed. If not, install it using pip:

pip install django

Create a new Django project:

django-admin startproject myproject
cd myproject

Create a new Django app:

python manage.py startapp myapp

Add the app to your project's settings. Open myproject/settings.py and add 'myapp' to INSTALLED_APPS:

INSTALLED_APPS = [
    ...
    'myapp',
]

Step 2: Creating Models

Let's create a model to represent some data we want to group by. In myapp/models.py, define a simple model with a field to group by and another field to aggregate.

Python

from django.db import models

class Item(models.Model):
    category = models.CharField(max_length=100)
    value = models.IntegerField()

    def __str__(self):
        return self.category

Run the following commands to create and apply migrations:

python manage.py makemigrations
python manage.py migrate

Step 3: Adding Sample Data

For demonstration purposes, add some sample data. You can use the Django admin interface for this. First, create an admin user:

python manage.py createsuperuser

Then, register the Item model in myapp/admin.py:

Python

from django.contrib import admin
from .models import Item

admin.site.register(Item)

Run the development server and add sample data through the admin interface:

python manage.py runserver

Step 4: Creating Views and URLs

Create a view to perform the GROUP BY query and display the results. In myapp/views.py, add the following code:

Python

from django.shortcuts import render
from django.db.models import Count, Sum
from .models import Item

def grouped_items(request):
    # Perform the GROUP BY query using Django's ORM
    grouped_data = Item.objects.values('category').annotate(
        total_value=Sum('value'),
        group_count=Count('id')
    )

    # Calculate the sum of all values
    total_sum = Item.objects.aggregate(total_sum=Sum('value'))['total_sum']

    return render(request, 'grouped_items.html', {
        'grouped_data': grouped_data,
        'total_sum': total_sum
    })

Create a template to display the grouped data. In myapp/templates/grouped_items.html, add the following code:

HTML

<!DOCTYPE html>
<html>
<head>
    <title>Grouped Items</title>
</head>
<body>
    <h1>Items Grouped by Category</h1>
    <ul>
        {% for item in grouped_data %}
        <li>
            {{ item.category }}: Total Value = {{ item.total_value }}
        </li>
        {% endfor %}
    </ul>
    <h2>Total Sum of All Values: {{ total_sum }}</h2>
</body>
</html>

Set up the URL routing for this view. In myapp/urls.py, add the following code:

Python

from django.urls import path
from .views import grouped_items

urlpatterns = [
    path('grouped/', grouped_items, name='grouped_items'),
]

Include this app's URLs in the project's URL configuration. Edit myproject/urls.py:

Python

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include('myapp.urls')),
]

Step 5: Running the Server

Run the development server to test your project:

python manage.py runserver

Visit http://127.0.0.1:8000/grouped/ in your browser. You should see a list of items grouped by category, displaying the total value for each category.