Django Aggregation: Summation of Multiplication of two fields

I have a model something like this:

class Task(models.Model):
    progress = models.PositiveIntegerField()
    estimated_days = models.PositiveIntegerField()

Now I would like to do a calculation Sum(progress * estimated_days) on the database level. Using Django Aggregation I can have the sum for each field but not the summation of multiplication of fields.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

With Django 1.8 and above you can now pass an expression to your aggregate:

 from django.db.models import F

 Task.objects.aggregate(total=Sum(F('progress') * F('estimated_days')))['total']

Constants are also available, and everything is combinable:

 from django.db.models import Value

 Task.objects.aggregate(total=Sum('progress') / Value(10))['total']

Method 2

Update: for Django >= 1.8 please follow the answer provided by @kmmbvnr

it’s possible using Django ORM:

here’s what you should do:

from django.db.models import Sum

total = ( Task.objects
            .filter(your-filter-here)
            .aggregate(
                total=Sum('progress', field="progress*estimated_days")
             )['total']
         )

Note: if the two fields are of different types, say integer & float, the type you want to return should be passed as the first parameter of Sum

It’s a late answer, but I guess it’ll help someone looking for the same.

Method 3

The solution depends on Django version.

• django < 1.8

  from django.db.models import Sum
  MyModel.objects.filter(<filters>).aggregate(Sum('field1', field="field1*field2"))

• django >= 1.8

  from django.db.models import Sum, F
  MyModel.objects.filter(<filters>).aggregate(Sum(F('field1')*F('field2')))

Method 4

Edited (after Django 1.8)

Since Django 1.8, you can use F:

from django.db.models import F. Sum

total = ( 
    Task
    .objects
    .aggregate(total=Sum(F('progress') * F('estimated_days'))) 
    ['total']
)

Old answer (before Django 1.8)

This answer was wrote on 2012 and django 1.8 was published on 2015

Do you have several options:

1. Raw query
2. Emulbreh’s undocumented approach
3. Create a third field progress_X_estimated_days and update it in save overwrited method. Then do aggregation through this new field.

Overwriting:

class Task(models.Model):
   progress = models.PositiveIntegerField()
   estimated_days = models.PositiveIntegerField()
   progress_X_estimated_days = models.PositiveIntegerField(editable=False)

   def save(self, *args, **kwargs):
      progress_X_estimated_days = self.progress * self.estimated_days
      super(Task, self).save(*args, **kwargs)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0