Grouping Data

Introduction

In SQL, group by statement is used to arrange similar records into groups using certain functions, grouping refers to finding the average of the data, the sum of the data, the maximum and minimum among the records, and the total count of the records. For example, if multiple records have the same values in a column, a group will be created containing these records. Group by statements are used with aggregate functions such as Count(), Max(), Min(), Sum(), and Avg(). Say we have a table named Students.

Now we want to know the sum of the marks both the students obtained in all the subjects, therefore we make a query. In this query, we are selecting the name and marks(finding the sum) of the students from the table, next we are grouping the records based on the name of the students so the record of each student is displayed with the sum of the marks they obtained.

We get the result as

Grouping Data in Django

In Django, the default database is SQLite. We can group records in the database and get a query set considering two or more columns. Let us create a model and add records to it so that we can group the records.

Register the model in theadmin.py file. This step is mandatory, it makes sure that the models we have created are registered for the ease of adding records to the table.

In the interactive console, after importing the models, let us add some records in the table Book and Bookdata we created.

Say we want to make a query in which we get the name of the book along with its price, we will be using annotate, which will be translated as a group by query.

Using Annotate to Group Data in Django

Annotation of an object creates a separate summary for each object in a queryset. This implies the fact that annotation is used to find the average, sum of the records, etc. It is often used to obtain a summary of a particular object. Below is the syntax for annotation.

In the above syntax,AggregateFunction is nothing but functions likeavg for finding the average, the sum for finding the sum of data, etc. Let us consider an example. First, we create a model.

Register the model in the admin.py file. This step is mandatory, it makes sure that the models we have created are registered for the ease of adding records to the table.

In the interactive console, after importing the models, let us add some records.

Now we want to know the total number of chapters in the book. Therefore, we use the annotated syntax. First, we import Count then we take an output variable chap_count in the syntax model_name.objects.annotate(), then we use count('bookdata'). To get the count we use the output variable along with the number of the records in the model book (the starting index of the records stored is 0) along with the variable which stores the count in the annotated syntax.

Count()

Count() in Django is used to count the number of records we entered into the database i.e. the total number of rows. It is mandatory to import the count command before using it. The syntax of the count command is

Let us consider the example given in the above section. After importing the models and the Count() command. We can find the number of entries we made in the particular model.

In the above query at first, we found the number of books we entered and in the second query, we get the total number of chapters as a whole we entered into our database.

Filter()

The filter() returns a query set with the objects we want, based on the conditions given in the filter() command. The syntax of the command is,

Let us consider the following example.

In the above code, we created two models Album and Song. Whenever an instance of a model is created in Django, it will display the object as Modelname Object(1) in the admin interface. Hence to change the display name we use the function def __str__(self). The Str function in a Django model returns a string that is rendered as the display name of instances for that model. In our case, it will display the name of the title for the Model Album and the name of the song for the Model Song. In the Model Song, we are linking the second field, the album with the Model Album. After creating the models, makemigrations is responsible for packing up the changes into individual migration files, and migrate is responsible for applying those to our database.

Now we need to access Django ORM, it can be accessed by using the following command inside our project directory:

This leads us to an interactive Python console. Next, we are supposed to import our models using the following command:

Let us add some records to the database.

We use a filter command to get the record with the genre as 'pop'

The corresponding SQL query is

Django also allows fetching filtered data based on multiple conditions, like AND and OR operations, and also be performed.

1. AND(&)

We can get filtered data satisfying both of the queries mentioned. Refer to the example for demonstration.

The corresponding SQL query is

2. OR(|)

We can also get filtered data that matches either of the query mentioned.

The corresponding SQL query is

Order by

Django sorting allows the user to sort the records in alphabetical order. The records can also be sorted in ascending order or descending order based on a single field or multiple fields. Let us create a model in the models.py file.

In the above code,

• We created a class Details under models.model.
• We created fields for our model or table that is name, mobile_no, and age.
• The name is assigned a character field.
• mobile_no is assigned an integer field.
• age is assigned an integer field.
• Underdef __str__, we specified self.name, which implies that our records will display the name when called.

Next, we register it in the admin.py file,

Now we go back to the command prompt and write the following code to migrate the changes.

Since we are done with the creation of the Django model, we can begin with Django sorting after entering the record in the model inside the interactive console. Let us go to the interactive console by entering the following command.

Inside the interactive console, we import the model Details and create the records and check whether the records we created exist or not. We write the command Details.objects.all() to display the records entered.

To sort the data alphabetically, we write 'name' insideorder_by:

To sort the data in the reverse alphabetical order, we write '-name' inside order_by. The minus '-' indicates that the ordering is done in reverse order.

Using Django to Sort Data in Ascending Order

Let us sort the records in ascending order based on a single field. The results are by default sorted in ascending order. Here we are sorting the records based on mobile numbers. Therefore, we write mobile_no inside order_by.

Let us now sort the records based on age. Therefore, we write age inside order_by.

Using Django to Sort Data in Descending Order

Let us sort the records in descending order based on a single field. Here we are sorting the records based on mobile numbers. Therefore, we write -mobile_no inside order_by. The minus '-' indicates that the ordering is done in reverse order.

Let us now sort the records based on age. Therefore, we write '-age' inside order_by. The minus '-' indicates that the ordering is done in reverse order.

Multiple Order by’s

Let us sort the records based on two fields. Here we are sorting the records based on the age of the records in ascending order and mobile numbers sorted in descending order. Therefore, in the order_by we add both the fields separated by a comma.