CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-querysetdjango-orm
Raunak Agarwal
Raunak Agarwal

Reputation: 7228

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.

Upvotes: 68

Views: 55464

Answers (5)

Veridiana Maricato
Veridiana Maricato

Reputation: 1

In your model, you can use the @property decorator before defining a method for performing calculations, like this:

class Task(models.Model):
    progress = models.PositiveIntegerField()
    estimated_days = models.PositiveIntegerField()
    
    @property
    def new_number(self):
        new_number = self.progress * self.estimated_days
        return new_number

Then, you can access it normally, calling {{task.new_number}} on the template, for example.

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

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)

Upvotes: 2

sha256
sha256

Reputation: 3099

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.

Upvotes: 79

Antstud
Antstud

Reputation: 779

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')))

Upvotes: 37

kmmbvnr
kmmbvnr

Reputation: 6139

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']

Upvotes: 105

Related Questions