Django Aggregations are database functions that perform operations on a set of values and return a single value as the result.

Aggregations in Django are performed using the annotate() and aggregate() methods on a QuerySet.

In the context of Django, aggregations are always performed on a QuerySet like below:

employee_data = Employee.objects.filter(
    # some conditions
)
employee_data.aggregate(Avg("field_name"))

Alternatively, you can directly call the aggregate() method on the object like this:

Employee.objects.aggregate(Avg("field_name"))

Key Aggregation Functions:

Django offers a variety of aggregation functions that can be used to analyse and summarise data. Some of the key aggregation functions include:

Count(): Counts the number of records.

Sum(): Calculates the sum of a numeric field.

Avg(): Computes the average of a numeric field.

Min(): Finds the minimum value in a field.

Max(): Retrieves the maximum value in a field.

Avg Function

The Avg function allows us to calculate the average of a numeric field for a set of records.

It returns a dictionary as the result, and the dictionary key is in the format <field>__avg.

Scenario:

You want to calculate the average salary of all the employees in the company.

Query:

from django.db.models import Avg
from employee. models import Salary

average_salary = Salary.objects.aggregate(Avg('salary_amount'))
print(average_salary)

# Expected Result
# {'salary_amount__avg': 69238.0}

In the above query, we call the aggregate method on the Salary model and pass Avg('salary_amount') as an argument to calculate the average salary for all employees.

The result is a dictionary containing the field name with the calculated average value.

The key of the result dictionary is <field>__avg, as in this example it is salary_amount__avg.

Equivalent SQL Query:

-- SQL Query to Calculate the Average Salary
SELECT AVG("employee_salary"."salary_amount") AS "salary_average" FROM "employee_salary";

Count Function

The Count function allows us to count the number of records that match specified conditions.

It returns a dictionary as the result with the key <field>__count.

Scenario:

You want to count the number of employees in your company.

Query:

# import the Count function
from django.db.models import Count
from employee. models import Employee

employee_count = Employee.objects.aggregate(Count('id'))
print(employee_count)

# Expected Result
{'id__count': 100}

In the above query, we call the aggregate method on the Employee model and pass Count('id') as an argument to count the number of employees.

The result is a dictionary containing the field name with the calculated count value.

The key of the result dictionary is in the format <field>__count, and in this example, it is id__count.

Equivalent SQL Query:

-- SQL Query to Calculate the Average Salary\
SELECT COUNT("employee_employee"."id") AS "id__count" FROM "employee_employee";

Max Function

The Max function allows us to find the maximum value of a numeric field for a set of records.

It returns a dictionary as the result with the key <field>__max.

Scenario:

You want to find the highest salary among all employees in your company.

Query:

# import the Max function
from django.db.models import Max
from employee. models import Salary

max_salary = Salary.objects.aggregate(Max('salary_amount'))
print(max_salary)

# Expected Result
{'salary_amount__max': 141000}

Note: If you want to customize the name of the result dictionary's key, use the alias as below:
max_salary = Salary.objects.aggregate(highest_salary = Max('salary_amount'))
The result will be as {'highest_salary': 141000}.

Equivalent SQL Query:

-- SQL Query to Calculate the Maximum Salary
SELECT MAX("employee_salary"."salary_amount") AS "highest_salary" FROM "employee_salary";

Min Function

The Min function allows us to find the minimum value of a numeric field for a set of records.

It returns a dictionary as the result with the key <field>__min.

Scenario:

You want to find the lowest salary among all employees in your company.

Query:

# import the Min function
from django.db.models import Min
from employee. models import Salary

min_salary = Salary.objects.aggregate(Min('salary_amount'))
print(min_salary)

# Expected Result
{'salary_amount__min': 43200}

Equivalent SQL Query:

-- SQL Query to Calculate the Minimum Salary
SELECT MIN("employee_salary"."salary_amount") AS "salary_amount__min" FROM "employee_salary";

StdDev Function

The StdDev function allows us to calculate the standard deviation of a numeric field for a set of records.

It returns a dictionary as the result, with the key in the format <field>__stddev.

Scenario:

You want to calculate the standard deviation of salaries among all employees in your company.

Query:

from django.db.models import StdDev
from employee. models import Salary

salary_stddev = Salary.objects.aggregate(StdDev('salary_amount'))
print(salary_stddev)

# Expected Result
{'salary_amount__stddev': 26935.904588485606}

Equivalent SQL Query:

SELECT STDDEV_POP("employee_salary"."salary_amount") AS "salary_amount__stddev" FROM "employee_salary";

Sum Function

The Sum function allows us to calculate the total sum of a numeric field for a set of records.

It returns a dictionary as the result, with the key in the format <field>__sum.

Scenario:

You want to calculate the total salary payout for all employees in your company.

Query

from django.db.models import Sum
from employee. models import Salary

total_salary_payout = Salary.objects.aggregate(Sum('salary_amount'))
print(total_salary_payout)

# Expected Result
# {'salary_amount__sum': 6923800}

Equivalent SQL Query:

SELECT SUM("employee_salary"."salary_amount") AS "salary_amount__sum" FROM "employee_salary";

Variance Function

The Variance function allows you to calculate the population variance of a numeric field for a set of records. It measures the spread of data points relative to their mean.

It returns a dictionary as the result, with the key in the format <field>__var.

Scenario:

You want to analyse how much salaries deviate from the average salary in your company. Variance is a good indicator of this spread.

Query:

from django.db.models import Variance
from employee. Equivalent SQL Query: models import Salary

salary_variance = Salary.objects.aggregate(Variance('salary_amount'))
print(salary_variance)

Equivalent SQL Query:

SELECT VAR_POP("employee_salary"."salary_amount") AS "salary_amount__variance" FROM "employee_salary";