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:
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 makemigrations
Code language: Python (python)
Output:
Migrations for 'hr':
hr\migrations\0005_employee_salary.py
- Add field salary to employee
Code language: Python (python)
Third, propagate the changes to the database by running the migrate
command:
python manage.py migrate
Code language: Python (python)
Output:
Operations to perform:
Apply all migrations: admin, auth, contenttypes, hr, sessions
Running migrations:
Applying hr.0005_employee_salary... OK
Code language: Python (python)
Finally, populate values into the salary
column with the data from data.json
fixture:
python manage.py loaddata data.json
Code language: Python (python)
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]
220
Code 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]
29
Code 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 aQuerySet
. - 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.