The GROUP BY clause is a clause in SQL (Structured Query Language) used to group rows with the same values in one or more columns and perform aggregate functions on each group.
SELECT column1, column2, ..., columnN, aggregate_function(column)FROM table_nameWHERE [conditions]GROUP BY column1, column2, ..., columnN;
In this syntax, column1, column2, ..., columnN are the columns that we want to group by, and aggregate_function(column) is one of the aggregate functions such as SUM, AVG, MIN, MAX, or COUNT that we want to apply to each group.
For example, if we have a table called sales with columns product, category, quantity, and price, and we want to know the total sales for each category, we can use the following query:
SELECT category, SUM(quantity*price) AS total_salesFROM salesGROUP BY category;
This query will group the rows by category and calculate the total sales for each category using the SUM aggregate function. The result will be a table with two columns: category and total_sales.
In Django, you can use the values() and annotate() methods to achieve the equivalent of a GROUP BY clause in SQL. Here are some examples:
Suppose you have a book model that looks like this:
class Book(models.Model):title = models.CharField(max_length=100)author = models.CharField(max_length=100)pub_date = models.DateField()rating = models.IntegerField()
You can group the books by their authors and calculate the average rating for each author using the values() and annotate() methods like this:
from django.db.models import Avgbooks_by_author = Book.objects.values('author').annotate(avg_rating=Avg('rating'))
The resulting query set will have one row for each author, with the author field and the calculated avg_rating field:
[{'author': 'J.K. Rowling', 'avg_rating': 4.5},{'author': 'George R.R. Martin', 'avg_rating': 4.2},{'author': 'Stephen King', 'avg_rating': 3.9},...]
You can also group the books by their publication year and count the number of books published in each year using the values() and annotate() methods like this:
from django.db.models import Countfrom datetime import datetimebooks_by_year = Book.objects.values(year=ExtractYear('pub_date')).annotate(count=Count('id'))
The resulting query set will have one row for each year, with the year field and the calculated count field:
[{'year': 2000, 'count': 10},{'year': 2001, 'count': 15},{'year': 2002, 'count': 12},...]
Note that in the second example, we used the ExtractYear() function to extract the year from the pub_date field. This function is provided by the django.db.models.functions module and is used to extract a specific component from a date or time field.
Let's test a Django app called "Library" with a model for books and authors. We will demonstrate how to group books by their authors and calculate the average rating for each author.
Bud1qlite3 db.sqlite3IlocblobA.������libraryIlocblob�.������library_projectIlocblob .������ manage.pyIlocblob�.������ @� @� @� @ EDSDB `� @� @� @
Let's get into the code inside the views.py file.
Lines 1–4: This part of the code imports the necessary view modules and models. It imports render to render HTML templates, Avg to calculate the average rating of books, Book model to interact with the database and query book data, and redirect to perform a redirection from one view to another.
Lines 6–7: The home view is a simple redirect view. When a user visits the home page, it will automatically redirect them to the authors_average_rating view. In simple words, when someone accesses the root URL of the application, they will be redirected to the page that shows the average rating of authors.
Lines 9–11: We implement the authors_average_rating view, when a user accesses the authors_average_rating URL, the view calculates the average rating of each author's books and displays this information in an HTML template. The template can then use the authors_avg_rating variable to display the data in a structured format on the page.
Free Resources