How to Query as GROUP BY in Django?

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.

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.

Reffered: https://www.geeksforgeeks.org

Python

Related
How to get GET request values in Django?
How to Define Two Fields "Unique" as Couple in Django
How to Compare List of Dictionaries in Python
How to Drop Negative Values in Pandas DataFrame
How to Import BeautifulSoup in Python

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	17