{"id":6445,"date":"2023-01-19T03:31:23","date_gmt":"2023-01-19T03:31:23","guid":{"rendered":"https:\/\/www.pythontutorial.net\/?page_id=6445"},"modified":"2023-01-19T03:54:11","modified_gmt":"2023-01-19T03:54:11","slug":"django-aggregate","status":"publish","type":"page","link":"https:\/\/www.pythontutorial.net\/django-tutorial\/django-aggregate\/","title":{"rendered":"Django Aggregate"},"content":{"rendered":"\n

Summary<\/strong>: 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.<\/p>\n\n\n\n

Preparing data #<\/a><\/h2>\n\n\n\n

We’ll use the Employee<\/code> and Department<\/code> models from the hr<\/code> application for the demonstration. The Employee<\/code> and Department<\/code> models map to the hr_employee<\/code> and hr_department<\/code> tables:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To start the tutorial, you can download the project source here<\/a> and follow these steps:<\/p>\n\n\n\n

First, add the salary<\/code> field to the Employee<\/code> model:<\/p>\n\n\n

class<\/span> Employee<\/span>(models.Model)<\/span>:<\/span>\n\n    salary = models.DecimalField(max_digits=15<\/span>, decimal_places=2<\/span>)\n    # ...<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
 python manage.py makemigrations<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Migrations for<\/span> 'hr'<\/span>:\n  hr\\migrations\\0005<\/span>_employee_salary.py\n    - Add field salary to employee<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, propagate the changes to the database by running the migrate<\/code> command:<\/p>\n\n\n
python manage.py migrate<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Operations to perform:\n  Apply all migrations: admin, auth, contenttypes, hr, sessions\nRunning migrations:\n  Applying hr.0005<\/span>_employee_salary... OK<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, populate values into the salary<\/code> column with the data from data.json<\/code> fixture:<\/p>\n\n\n
python manage.py loaddata data.json<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Introduction to the Django aggregate #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Count #<\/a><\/h3>\n\n\n\n
The QuerySet<\/code> object provides you with the count()<\/code><\/code> method that returns the number of objects it contains. For example, you can use the count()<\/code><\/code> method to get the number of employees:<\/p>\n\n\n
>>> <\/span>Employee.objects.count()\nSELECT COUNT(*) AS \"__count\"<\/span>\n  FROM \"hr_employee\"<\/span>        \nExecution time: 0.002160<\/span>s [Database: default]\n220<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the number of employees whose first names start with<\/a> the letter J<\/code>, you can use both filter()<\/code> and count()<\/code> methods of the QuerySet<\/code> object like this:<\/p>\n\n\n
>>> <\/span>Employee.objects.filter(first_name__startswith='J'<\/span>).count()\nSELECT COUNT(*) AS \"__count\"<\/span>\n  FROM \"hr_employee\"<\/span>\n WHERE \"hr_employee\"<\/span>.\"first_name\"<\/span>::text LIKE 'J%'<\/span>\nExecution time: 0.000000<\/span>s [Database: default]\n29<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the filter()<\/code> method forms a WHERE<\/code> clause while the count()<\/code> method forms the COUNT()<\/code> function.<\/p>\n\n\n\n
Max #<\/a><\/h3>\n\n\n\n
The Max()<\/code> returns the maximum value in a set of values. It accepts a column that you want to get the highest value. <\/p>\n\n\n\n
For example, the following uses the Max()<\/code> to return the highest salary:<\/p>\n\n\n
>>> <\/span>Employee.objects.aggregate(Max('salary'<\/span>))\nSELECT MAX(\"hr_employee\"<\/span>.\"salary\"<\/span>) AS \"salary__max\"<\/span>\n  FROM \"hr_employee\"<\/span>\nExecution time: 0.002001<\/span>s [Database: default]\n{'salary__max'<\/span>: Decimal('248312.00'<\/span>)}<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Min #<\/a><\/h3>\n\n\n\n
The Min()<\/code> returns the minimum value in a set of values. Like the Max()<\/code>, it accepts a column that you want to get the lowest value.<\/p>\n\n\n\n
The following example uses the Min()<\/code> to return the lowest salary of employees:<\/p>\n\n\n
>>> <\/span>Employee.objects.aggregate(Min('salary'<\/span>)) \nSELECT MIN(\"hr_employee\"<\/span>.\"salary\"<\/span>) AS \"salary__min\"<\/span>\n  FROM \"hr_employee\"<\/span>\nExecution time: 0.002015<\/span>s [Database: default]\n{'salary__min'<\/span>: Decimal('40543.00'<\/span>)}<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Avg #<\/a><\/h3>\n\n\n\n
The Avg()<\/code> 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:<\/p>\n\n\n
>>> <\/span>Employee.objects.aggregate(Avg('salary'<\/span>)) \nSELECT AVG(\"hr_employee\"<\/span>.\"salary\"<\/span>) AS \"salary__avg\"<\/span>\n  FROM \"hr_employee\"<\/span>\nExecution time: 0.005468<\/span>s [Database: default]\n{'salary__avg'<\/span>: Decimal('137100.490909090909'<\/span>)}<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Sum #<\/a><\/h2>\n\n\n\n
The Sum()<\/code><\/code> returns the sum of values.  For example, you can use the Sum()<\/code><\/code> to calculate the total salary of the company:<\/p>\n\n\n
>>> <\/span>Employee.objects.aggregate(Sum('salary'<\/span>)) \nSELECT SUM(\"hr_employee\"<\/span>.\"salary\"<\/span>) AS \"salary__sum\"<\/span>\n  FROM \"hr_employee\"<\/span>\nExecution time: 0.000140<\/span>s [Database: default]\n{'salary__sum'<\/span>: Decimal('30162108.00'<\/span>)}<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the count()<\/code> method to get the number of objects of a QuerySet<\/code>.<\/li>\n\n\n\n
  • Use the Max()<\/code> to get the maximum value in a set of values.<\/li>\n\n\n\n
  • Use the Min()<\/code> to get the minimum value in a set of values.<\/li>\n\n\n\n
  • Use the Avg()<\/code> to get the average value in a set of values.<\/li>\n\n\n\n
  • Use the Sum()<\/code> to get the total value of a set.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t
The Sum()<\/code> executes the SQL SUM()<\/a><\/code> function and returns the total value of all the values in the salary column of the hr_employee<\/code> table.<\/p>\n\n\n\n
Behind the scenes, the Avg()<\/code> executes the SQL AVG()<\/a><\/code> function on the salary column of the hr_employee<\/code> and returns the average salary.<\/p>\n\n\n\n
The Min()<\/code> function executes the SQL MIN()<\/a><\/code> function that returns the minimum value in the salary column.<\/p>\n\n\n\n
The Max()<\/code> executes the SQL MAX()<\/a><\/code> on the salary column of the hr_employee<\/code> table and returns the highest salary.<\/p>\n\n\n\n
The count()<\/code> method uses the SQL COUNT(*)<\/a><\/code> function to return the number of rows in the hr_employee<\/code> table.<\/p>\n\n\n\n
Download the complete project source code here.<\/a><\/p>\n\n\n\n
Second, make migrations<\/a> using the makemigrations<\/code> command:<\/p>\n\n\n