Django Aggregate

Summary: in this tutorial, you’ll learn how to use Django to get the aggregate values from the database including count, min, max, sum, and avg.

Preparing data

We’ll use the Employee and Department models from the hr application for the demonstration. The Employee and Department models map to the hr_employee and hr_department tables:

To start the tutorial, you can download the project source here and follow these steps:

First, add the salary field to the Employee model:

class Employee(models.Model):

    salary = models.DecimalField(max_digits=15, decimal_places=2)
    # ...Code language: Python (python)

Second, make migrations using the makemigrations command:

 python manage.py makemigrationsCode language: Python (python)

Output:

Migrations for 'hr':
  hr\migrations\0005_employee_salary.py
    - Add field salary to employeeCode language: Python (python)

Third, propagate the changes to the database by running the migrate command:

python manage.py migrateCode language: Python (python)

Output:

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, hr, sessions
Running migrations:
  Applying hr.0005_employee_salary... OKCode language: Python (python)

Finally, populate values into the salary column with the data from data.json fixture:

python manage.py loaddata data.jsonCode language: Python (python)

Download the complete project source code here.

Introduction to the Django aggregate

An aggregate function accepts a list of values and returns a single value. The commonly used aggregate functions are count, max, min, avg, and sum.

Count

The QuerySet object provides you with the count() method that returns the number of objects it contains. For example, you can use the count() method to get the number of employees:

>>> Employee.objects.count()
SELECT COUNT(*) AS "__count"
  FROM "hr_employee"        
Execution time: 0.002160s [Database: default]
220Code language: Python (python)

The count() method uses the SQL COUNT(*) function to return the number of rows in the hr_employee table.

To get the number of employees whose first names start with the letter J, you can use both filter() and count() methods of the QuerySet object like this:

>>> Employee.objects.filter(first_name__startswith='J').count()
SELECT COUNT(*) AS "__count"
  FROM "hr_employee"
 WHERE "hr_employee"."first_name"::text LIKE 'J%'
Execution time: 0.000000s [Database: default]
29Code language: Python (python)

In this case, the filter() method forms a WHERE clause while the count() method forms the COUNT() function.

Max

The Max() returns the maximum value in a set of values. It accepts a column that you want to get the highest value.

For example, the following uses the Max() to return the highest salary:

>>> Employee.objects.aggregate(Max('salary'))
SELECT MAX("hr_employee"."salary") AS "salary__max"
  FROM "hr_employee"
Execution time: 0.002001s [Database: default]
{'salary__max': Decimal('248312.00')}Code language: Python (python)

The Max() executes the SQL MAX() on the salary column of the hr_employee table and returns the highest salary.

Min

The Min() returns the minimum value in a set of values. Like the Max(), it accepts a column that you want to get the lowest value.

The following example uses the Min() to return the lowest salary of employees:

>>> Employee.objects.aggregate(Min('salary')) 
SELECT MIN("hr_employee"."salary") AS "salary__min"
  FROM "hr_employee"
Execution time: 0.002015s [Database: default]
{'salary__min': Decimal('40543.00')}Code language: Python (python)

The Min() function executes the SQL MIN() function that returns the minimum value in the salary column.

Avg

The Avg() returns the average value in a set of values. It accepts a column name and returns the average value of all the values in that column:

>>> Employee.objects.aggregate(Avg('salary')) 
SELECT AVG("hr_employee"."salary") AS "salary__avg"
  FROM "hr_employee"
Execution time: 0.005468s [Database: default]
{'salary__avg': Decimal('137100.490909090909')}Code language: Python (python)

Behind the scenes, the Avg() executes the SQL AVG() function on the salary column of the hr_employee and returns the average salary.

Sum

The Sum() returns the sum of values. For example, you can use the Sum() to calculate the total salary of the company:

>>> Employee.objects.aggregate(Sum('salary')) 
SELECT SUM("hr_employee"."salary") AS "salary__sum"
  FROM "hr_employee"
Execution time: 0.000140s [Database: default]
{'salary__sum': Decimal('30162108.00')}Code language: Python (python)

The Sum() executes the SQL SUM() function and returns the total value of all the values in the salary column of the hr_employee table.

Summary

• Use the count() method to get the number of objects of a QuerySet.
• Use the Max() to get the maximum value in a set of values.
• Use the Min() to get the minimum value in a set of values.
• Use the Avg() to get the average value in a set of values.
• Use the Sum() to get the total value of a set.