Python Django group by

In this Django tutorial, we will learn about the “Python Django group by” and we will also discuss some examples related to it. These are the following topics that we are going to discuss in this tutorial.

  • Python Django group by
  • Python Django group by annotate
  • Python Django group by filter
  • Python Django group by count
  • Python Django group by avg
  • Python Django group by multiple fields
  • Python Django group by expression
  • Python Django group by distinct
  • Python Django model group by
  • Python Django orm group by

Python Django group by

So, in this section, we will understand how to use the GROUP BY clause in Django ORM. Now, if you are familiar with SQL, you might be aware of the GROUP BY statement.

The GROUP BY statement is used to combine rows with the same values, and it is mostly used with aggrege functions like COUNT(), AVG(), SUM(), etc. Whereas, the Django ORM is an abstraction layer that allows us to work with the database by translating the python code into a SQL statement.

In Django, we can use the GROUP BY statement by using the aggregate function or by using some built-ins. Also, the GROUP BY statement and the aggregate function are always used in combinations to group the result set.

And in this section, we will learn to use the aggregate function in Django to implement the GROUP BY statement. Let’s understand this with the help of a simple example.

For this example demonstration, we are going to use the Customer model with the following data.

NameAddressCountryCity
Astra De Fries8268 Texas CircleUnited StatesBuffalo
Bowie McSperron3 Eggendart TrailUnited StatesClearwater
Clarinda Kemme78214 Buell PassCanadaShediac
Shane Frensch35456 Trailsway HillUnited StatesDetroit
Cortney Davidsson94999 Little Fleur DriveUnited KingdomKinloch
Queenie Gravie8860 Norway Maple PointUnited KingdomAshley
Dulcy Fishlock85095 Debs CrossingUnited StatesMiami Beach
Teodorico Puzey04977 Chive CircleCanadaBowen Island
Customer Model

For example, we will simply use an aggregate function COUNT() to count the total number of records that are there in the Customer model.

(InteractiveConsole)
>>>
>>> from myApp.models import Customer
>>> from django.db.models import Count
>>>
>>> queryset = Customer.objects.aggregate(total_count=Count('id'))
>>>
>>> print(queryset)

In this example, first, we simply imported the Customer model, and then, we imported the Count() function. After this, we created a QuerySet using an aggregate method and in the method, we have used the Count() function. As there are 10 records in the model, the queryset will return a dictionary with key as total_count, and value as 10.

Python Django group by
Result

Read: Python Django vs Flask

Python Django group by annotate

Now, whenever we use the aggregate, the aggregate function is applied to the whole table. But, we generally apply the GROUP BY statement on some groups of rows. For this execution, we have to use the annotate() method. Let’s have a simple example for this implementation.

Here is the code for this example.

(InteractiveConsole)
>>>
>>> from django.contrib.auth.models import User
>>> from django.db.models import Count
>>>
>>> queryset = User.objects.values('is_active').annotate(count=Count('id')).order_by()
>>>
>>> print(queryset)
  • In the above code, we are using the User model, and this time, we are looking for the total count of active users.
  • So, after importing the User model and Count() function, we are creating QuerySet.
  • And in the QuerySet, we are using values(‘is_active’) to select all the active users. And then, we are using the annotate(count=Count(‘id’)) to apply the aggregate function based upon the primary key.
  • In the end, we are using the print statement to print the QuerySet.
Python Django group by using annotate
Example with output

So, in the above example, values() and annotate() together can be used for the implementation of the GROUP BY statement. We can also confirm this by using the following code.

>>> str(queryet.query)

It will return the equivalent SQL statement for the given QuerySet. And in the result, we can easily see the use of the GROUP BY statement.

Python Django orm group by
SQL Query

Read: How to install Django

Python Django group by filter

Many times we only need to count a particular row or column instead of a group of columns. In such cases, we can use the filter() method with values() and annotate() to get the desired result.

Let’s understand the use of the filter() method with the help of an example. For this, again, we are using the built-in User model and its data. And we will try to fetch the count of all the active staff users from the User model.

(InteractiveConsole)
>>>
>>> from django.contrib.auth.models import User
>>> from django.db.models import Count
>>>
>>> queryset = User.objects.values('is_active').filter(is_staff=True).annotate(total_count=Count('id'))
>>>
>>> print(queryset)

In the code, we have used the filter method to select all the staff members from the User model. And similar to the previous example, we have used the values() and annotate() method for GROUP BY.

Here is the screenshot of the above code with output.

Python Django group by using filter
Example with output

And here is the equivalent SQL query for the above code.

Python Django orm group by filter
SQL Query

Read: Difference between app and project in Django

Python Django group by count

In this section, we will understand how to get a count of a column based upon grouping multiple rows. Let’s understand this with the help of an example. And this time, we are using the Customer model that we have shown in the first section.

For the demonstration, we will fetch the count of each unique country that is there in the country column. And here is the code of this example.

(InteractiveConsole)
>>>
>>> from myApp.models import Customer
>>> from django.db.models import Count
>>>
>>> queryset = Customer.objects.values('country').annotate(count=Count('country'))
>>>
>>> print(queryset)

In the above example, we have used the values() and annotate() method to get the country of counties that are there in the country column. Here is the result of this example.

Python Django group by count
Output

Read: How to setup Django project

Python Django group by multiple fields

In this section, we will understand how to group multiple fields by implementing the GROUP BY statement in Django. Now, for this implementation, we will the values() method to specify multiple fields.

For better understanding let’s execute an example related to it. And for this, we will use the built-in User model. The code for the example is as follows.

(InteractiveConsole)
>>>
>>> from django.contrib.auth.models import User
>>> from django.db.models import Count
>>>
>>> queryset = User.objects.values('is_active','is_staff').annotate(total_count=Count('id'))
>>>
>>> print(queryset)

In the above example, we have passed 2 fields in the values() method first is is_active, and the second is is_staff. And we are also using the Count() aggregate function. So, this QuerySet will return the count of users who belongs or does not belongs to the is_active or is_staff group. Here is the output of the example.

Python Django group by multiple fields
Output

Read: How to Create model in Django

Python Django group by expression

In this section, we will understand how to group value based upon an expression. Let’s understand the use of group expression with the help of an example. And we will be using the Employee model with the following data.

NameJob TitleJoining DateSalary
Hardy MuncerSenior Financial Analyst2021-05-2524000
Ric RuffleProduct Engineer2021-02-1340000
Rhoda BenzGeneral Manager2020-10-1735000
Rustin FlippelliFinancial Analyst2021-06-0420000
Denys ButtingTechnical Writer2020-11-2332000
Allene JohanssonDatabase Administrator2021-04-2128000
Amye RoonyAccounting Assistant2020-10-0223000
Willie PhilpsEngineer II2021-08-0132000
Employee Model

Now, we will try to fetch the count of the employees who joined each year. And the code for the example is given below.

(InteractiveConsole)
>>>
>>> from myApp.models import Employee
>>> from django.db.models import Count
>>>
>>> queryset = Employee.objects.values('joining_date__year').annotate(total_count=Count('id'))
>>>
>>> print(queryset)

In the above example, to fetch the date from the Datetime value, we have passed an expression <field>__year to the values() method. And it will return a dictionary with keys and values. Here is the screenshot of the result.

Python Django group by expression
Output

Read: How to Get Current time in Django

Python Django group by distinct

If we are working on a data-driven application using Django then, most of the time our tables might have some duplicate values. And while working with some aggregate functions like COUNT(), AVG(), etc, we might require a result based upon distinct occurrences. So in this section, we will discuss how to group data based upon distinct values in Django.

In terms of SQL, we use the SELECT DISTINCT statement to select different values from the table. Now, let’s understand how we can use this DISTINCT in our QuerySet.

Let’s understand this by demonstrating an example and for this, were using the Customer model.

Note: You can view the data of the Customer model in the first section of this article.

For this example demonstrates, we will fetch all the different country names and their count from the Customer model. And for the implementation, we are using Django shell. Here is the code that we have implemented.

(InteractiveConsole)
>>>
>>> from myApp.models import Customer
>>> from django.db.models import Count
>>>
>>> queryset = Customer.objects.values('country').annotate(count=Count('id'),unique_names=Count('country',distinct=True))
>>>
>>> print(queryset)

To select different country names, we have given “distinct=True” in the annotate() method. So, this QuerySet will return the count of each unique country name. For better understanding, here is the SQL query for the above QuerySet.

SELECT "myApp_customer"."country", COUNT("myApp_customer"."id") AS "count", COUNT(DISTINCT "myApp_customer"."country") AS "unique_names" FROM "myApp_customer" GROUP BY "myApp_customer"."country"

In the end, we will get the following output, as shown in the screenshot below.

Python Django group by distinct
Use of Distinct with GROUP BY in Django

Read: Python Django get admin password

Python Django group by avg

In Django, the grouping of records can be done using different aggregate functions like COUNT(), AVG(), MIN(), MAX(), etc. So, in this section, we will understand how to use the AVG() method to get the average value from the group of records in Django.

Let’s take an example for the execution of the AVG() method in Django. For this demonstration, we will use the Employee model which has the following data.

NameJob TitleJoining DateSalary
Hardy MuncerSenior Financial Analyst2021-05-2524000
Ric RuffleProduct Engineer2021-02-1340000
Rhoda BenzGeneral Manager2020-10-1735000
Rustin FlippelliFinancial Analyst2021-06-0420000
Denys ButtingTechnical Writer2020-11-2332000
Allene JohanssonDatabase Administrator2021-04-2128000
Amye RoonyAccounting Assistant2020-10-0223000
Willie PhilpsEngineer II2021-08-0132000
Employee Model

Now, we will try to fetch the average salary of the employees by running the following code.

(InteractiveConsole)
>>>
>>> from myApp.models import Employee
>>> from django.db.models import Avg
>>>
>>> queryset = Employee.objects.aggregate(Avg('salary'))
>>>
>>> print(queryset)

So, first, we have imported the Employee model and Avg() method. After this, we are using the aggregate function to get the average employee salary. Here is the image of this code with its output.

Python Django group by avg
Output

Read: Django for loop

In this Django tutorial, we have discussed the “Python Django group by” and we have also covered some examples related to it. These are the following topics that we have discussed in this tutorial.

  • Python Django group by
  • Python Django group by annotate
  • Python Django group by filter
  • Python Django group by count
  • Python Django group by avg
  • Python Django group by multiple fields
  • Python Django group by expression
  • Python Django group by distinct
  • Python Django model group by
  • Python Django orm group by