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.
| Name | Address | Country | City |
|---|---|---|---|
| Astra De Fries | 8268 Texas Circle | United States | Buffalo |
| Bowie McSperron | 3 Eggendart Trail | United States | Clearwater |
| Clarinda Kemme | 78214 Buell Pass | Canada | Shediac |
| Shane Frensch | 35456 Trailsway Hill | United States | Detroit |
| Cortney Davidsson | 94999 Little Fleur Drive | United Kingdom | Kinloch |
| Queenie Gravie | 8860 Norway Maple Point | United Kingdom | Ashley |
| Dulcy Fishlock | 85095 Debs Crossing | United States | Miami Beach |
| Teodorico Puzey | 04977 Chive Circle | Canada | Bowen Island |
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.
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.
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.
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.
And here is the equivalent SQL query for the above code.
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.
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.
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.
| Name | Job Title | Joining Date | Salary |
|---|---|---|---|
| Hardy Muncer | Senior Financial Analyst | 2021-05-25 | 24000 |
| Ric Ruffle | Product Engineer | 2021-02-13 | 40000 |
| Rhoda Benz | General Manager | 2020-10-17 | 35000 |
| Rustin Flippelli | Financial Analyst | 2021-06-04 | 20000 |
| Denys Butting | Technical Writer | 2020-11-23 | 32000 |
| Allene Johansson | Database Administrator | 2021-04-21 | 28000 |
| Amye Roony | Accounting Assistant | 2020-10-02 | 23000 |
| Willie Philps | Engineer II | 2021-08-01 | 32000 |
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.
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.
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.
| Name | Job Title | Joining Date | Salary |
|---|---|---|---|
| Hardy Muncer | Senior Financial Analyst | 2021-05-25 | 24000 |
| Ric Ruffle | Product Engineer | 2021-02-13 | 40000 |
| Rhoda Benz | General Manager | 2020-10-17 | 35000 |
| Rustin Flippelli | Financial Analyst | 2021-06-04 | 20000 |
| Denys Butting | Technical Writer | 2020-11-23 | 32000 |
| Allene Johansson | Database Administrator | 2021-04-21 | 28000 |
| Amye Roony | Accounting Assistant | 2020-10-02 | 23000 |
| Willie Philps | Engineer II | 2021-08-01 | 32000 |
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.
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
I am Bijay Kumar, a Microsoft MVP in SharePoint. Apart from SharePoint, I started working on Python, Machine learning, and artificial intelligence for the last 5 years. During this time I got expertise in various Python libraries also like Tkinter, Pandas, NumPy, Turtle, Django, Matplotlib, Tensorflow, Scipy, Scikit-Learn, etc… for various clients in the United States, Canada, the United Kingdom, Australia, New Zealand, etc. Check out my profile.