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

So, in this section, you 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, you 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, you 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, you 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, you 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, you simply import the Customer model, and then, you import the Count() function. After this, you create a QuerySet using an aggregate method and in the method, use the Count() function to count the number of records.

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

In the output, you can see that the customer table contains the 10 records only.

Read: Python Django vs Flask

Python Django Group By Annotate

Now, whenever you use the aggregate, the aggregate function is applied to the whole table. But, you generally apply the GROUP BY statement on some groups of rows.

For this execution, you 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, you are using the User model, and this time, you 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, you are using values(‘is_active’) to select all the active users. And then, using the annotate(count=Count(‘id’)) to apply the aggregate function based upon the primary key.
  • In the end, using the print statement to print the QuerySet.
Python Django group by using annotate

So, in the above example, values() and annotate() together can be used for the implementation of the GROUP BY statement. You 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, you can easily see the use of the GROUP BY statement.

Python Django orm group by

Read: How to install Django

Python Django Group By Filter

Many times you only need to count a particular row or column instead of a group of columns. In such cases, you 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, you 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, you 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

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

Python Django orm group by filter

Read: Difference between app and project in Django

Python Django Group By Count

In this section, you 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, you are using the Customer model that you have shown in the first section.

For the demonstration, you 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, you 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

Read: How to setup Django project

Python Django Group By Multiple Fields

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

For better understanding let’s execute an example related to it. And for this, you 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, you have passed 2 fields in the values() method first is is_active, and the second is is_staff. And you 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

Read: How to Create model in Django

Python Django Group By Expression

In this section, you 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 you 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, you 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, you 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

Read: How to Get Current time in Django

Python Django Group By Distinct

If you 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, you might require a result based upon distinct occurrences. So in this section, you will discuss how to group data based upon distinct values in Django.

In terms of SQL, you use the SELECT DISTINCT statement to select different values from the table. Now, let’s understand how you 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, you 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, you will get the following output, as shown in the screenshot below.

Python Django group by distinct

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, you 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, you 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, you 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, you have imported the Employee model and Avg() method. After this, you 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

Read: Django for loop

Conclusion

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