GROUP BY is a clause used in SQL (Structured Query Language) to group rows that have the same values in specified columns into summary rows. This is particularly useful when you want to perform aggregate functions (like counting, summing, averaging, etc.) on subsets of data based on common characteristics.

When you use GROUP BY, the result set is divided into groups based on the specified columns, and aggregate functions are applied to each group separately. It is commonly used with aggregate functions like COUNT, SUM, AVG, etc.

For understanding GROUP BY Properly, please checkout this link.

Assume a Customer model, storing essential information like name, city, state, and country. This table is designed to manage individual customer records, capturing contact details and geographic information.

from django.db import models

class Customer(models.Model):
    name = models.CharField(max_length=255)
    city = models.CharField(max_length=255)
    state = models.CharField(max_length=255)
    country = models.CharField(max_length=255)

Assume an example data table below:

Django group by single field:

In Django, you can use the values and annotate functions to perform aggregation queries. Here's an example of how you might perform the aggregation to get the total number of customers per country

from your_app.models import Customer
from django.db.models import Count

aggregated_data = Customer.objects.values('country').annotate(total_customers=Count('id'))

Output:

[
    {'country': 'USA', 'total_customers': 4},
    {'country': 'UK', 'total_customers': 4},
    {'country': 'Germany', 'total_customers': 4},
]

Django group by two fields:

This query groups the customers by both the country and state fields and calculates the total number of customers for each unique combination of country and state. Adjust the query based on your specific requirements.

from your_app.models import Customer
from django.db.models import Count


aggregated_data = Customer.objects.values('country', 'state').annotate(total_customers=Count('id'))

Output:

[
    {'country': 'USA', 'state': 'New York', 'total_customers': 3},
    {'country': 'USA', 'state': 'California', 'total_customers': 1},
    {'country': 'USA', 'state': 'Texas', 'total_customers': 1},
    {'country': 'UK', 'state': 'England', 'total_customers': 2},
    {'country': 'UK', 'state': 'Scotland', 'total_customers': 1},
    {'country': 'Germany', 'state': 'Moscow', 'total_customers': 1},
    {'country': 'Germany', 'state': 'Saint Petersburg', 'total_customers': 2},
    # ... additional rows based on your data
]

Django group by three fields:

This query groups the customers by the country , city and  state fields and calculates the total number of customers for each unique combination of country, city and state. Adjust the query based on your specific requirements.

from your_app.models import Customer
from django.db.models import Count


aggregated_data = Customer.objects.values('country', 'state', 'city').annotate(total_customers=Count('id'))

Output:

[
    {'country': 'USA', 'state': 'New York', 'city': 'New York City', 'total_customers': 1},
    {'country': 'USA', 'state': 'California', 'city': 'Los Angeles', 'total_customers': 1},
    {'country': 'USA', 'state': 'Illinois', 'city': 'Chicago', 'total_customers': 1},
    {'country': 'USA', 'state': 'Texas', 'city': 'Houston', 'total_customers': 1},
    {'country': 'UK', 'state': 'England', 'city': 'London', 'total_customers': 1},
    {'country': 'UK', 'state': 'England', 'city': 'Manchester', 'total_customers': 1},
    {'country': 'Germany', 'state': 'Moscow', 'city': 'Moscow', 'total_customers': 1},
    {'country': 'Germany', 'state': 'Saint Petersburg', 'city': 'Saint Petersburg', 'total_customers': 1},
    {'country': 'UK', 'state': 'England', 'city': 'Birmingham', 'total_customers': 1},
    {'country': 'UK', 'state': 'Scotland', 'city': 'Edinburgh', 'total_customers': 1},
    {'country': 'USA', 'state': 'Alabama', 'city': 'Birmingham', 'total_customers': 1},
    {'country': 'Germany', 'state': 'Scotland', 'city': 'Glasgow', 'total_customers': 1},
]

In conclusion, leveraging the power of Django's robust ORM capabilities allows developers to seamlessly perform grouping and aggregation operations on multiple fields within their models. In this article, we've delved into the efficient use of the values and annotate functions to aggregate data based on various criteria. Whether it's summarizing customer information or analyzing complex datasets, Django's versatile tools empower developers to handle diverse scenarios with elegance and clarity. By harnessing the expressive syntax of Django queries, developers can craft nuanced and powerful solutions, making group-by operations an essential feature for building sophisticated web applications.