Django GROUP BY

Updated May 20, 2023

Introduction to Django GROUP BY

The following article provides an outline for Django GROUP BY. This operation is fairly common for those more familiar with the SQL part. The Django ORM is said to be an abstraction layer that lets us work with the database (which is object-oriented), where finally, it’s just a relational database. All the operations are translated into SQL statements. The database is a powerful tool for doing the work much faster and directly. When you get through the GROUP BY Queries, you will no longer be interested in each model instance and table row details.

GROUP BY in Django with SQL (Using Django Querysets & SQL)

We used to have chaos among GROUP BY and aggregate. Both are different, but they can’t work without one another.

A GROUP BY in Django is used with the AGGREGATE functions or the built-ins. The GROUP BY statement and aggregate functions like COUNT, MAX, MIN, SUM, and AVG are combined to group the result – set by one or more columns.

Syntax:

SELECT column _ name(s)
FROM table _ name
WHERE condition
GROUP BY column _ name(s)
ORDER BY column _ name (s);

Understanding this clause using the DJANGO GROUP BY Cheat Sheet.

This is the model from Django’s built-in Django. contrib.auth app.

Count Rows:

Counting rows is one of the Django functions on the Query Set. Several rows in a table can be identified using the method of Count Rows. Here the key name is derived from the ‘Name of the field’ and the ‘Name of the aggregate’.

Example:

The following SQL statement lists the no. of customers from each country:

Sample Database:

Code:

SELECT COUNT (CustomerID), Country
FROM Customers
GROUP BY Country;

Output:

Example:

Count how many users we have.

Code:

SQL:

SELECT
COUNT(id) as total
FROM
auth_user;

Python:

from django.db.models import Count
queryset = (User.objects
.aggregate(
total=Count('id'),
)
)

Output:

Implementing the Process GROUP BY

When we use ‘aggregate’, the aggregate function is applied throughout the table (on the entire table). Even though this process makes work simple by applying the function on the entire table, the usual way is to apply ‘aggregation’ only on groups of rows.

Active users counting using GROUP BY:

Code:

SQL:

SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
GROUP BY
is_active

Python:

queryset = (User.objects
.values(
'is_active',
)
.annotate(
total=Count('id'),
)
.order_by()
)

Output:

***A combination of ‘VALUES’ & ‘ANNOTATE’ should produce a GROUP BY.

The aggregate results will not be produced if we fail to call VALUES before annotating. So, the ORDER is one of the most important things to remember while doing the GROUP BY function.

Using Filter in a Query Set:

When a particular or precise column or row needs to be counted, we should use ‘filter’. In the process of applying aggregation in a filtered query, we can use ‘filter’ anywhere in the query set.

Code:

SQL:

SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
WHERE
is_staff = True
GROUP BY
is_active

Python:

queryset = (User.objects
.values(
'is_active',
)
.filter(
is_staff=True,
)
.annotate(
total=Count('id'),
)
)

Like a filter, ‘order_by’ is anywhere in the query to SORT a query set. And for our convenience, we can use both the ‘GROUP BY’ and the ‘aggregate’ fields.

Code:

SQL:

SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
WHERE
is_staff = True
GROUP BY
is_active
ORDER BY
is_active,
total

Python:

queryset = (User.objects
.values(
'is_active',
)
.filter(
is_staff=True,
)
.annotate(
total=Count('id'),
)
.order_by(
'is_staff',
'total',
)
)

Output:

Conditional Aggregation in GROUP BY

To aggregate a part of the group, we should use Conditional Aggregation.

Example:

To count the number of staff and non-staff users by the year they signed upCode.

Code:

SELECT
EXTRACT('year' FROM date_jointed),
COUNT(id) FILTER (
WHERE is_employee = True
) AS employee_users,
COUNT(id) FILTER (
WHERE is_employee = False
) AS non_employee_users
FROM
authentic_user
GROUP BY
EXTRACT('year' FROM date_joined)
From Django.db.models import F, Q
(User.objects
.values('date_joined__year')
.annotate(
employee_users=(
Count('id', filter=Q(is_employee=True))
),
non_employee_users=(
Count('id', filter=Q(is_employee=False))
),
))

Having Clause

The WHERE keyword and the aggregate function should not be used in SQL. Instead, the HAVING clause is added to the SQL.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Usage of Having GROUP BY

To filter out the required data from the result of an aggregate function, the HAVING clause is used.

Sample Case:

Code:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Output:

GROUP BY – Distinct:

Sometimes, it would be desirable only to count the DISTINCT OCCURRENCES in a COUNT function.

Sample:

To count the number of different last names being there per user’s active status.

SELECT
is_active,
COUNT(id) AS total,
COUNT(DISTINCT last_name) AS unique_names
FROM
auth_user
GROUP BY
is_active
(User.objects
.values('is_active')
.annotate(
total=Count('id'),
unique_names=Count('last_name' distinct=True),
))