58

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.

funnydman
  • 2,957
  • 3
  • 13
  • 29
Raunak Agarwal
  • 6,637
  • 6
  • 34
  • 60

4 Answers4

86

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']
ghickman
  • 5,393
  • 6
  • 37
  • 51
kmmbvnr
  • 5,127
  • 2
  • 32
  • 39
  • 6
    I think this answer is easier to understand and maintain. Beside if the types of these field are different, add `output_field` parameter to aggregate function – Neo Ko Nov 23 '15 at 06:44
  • 1
    I am trying it on django 1.7 but I am getting AttributeError `AttributeError: 'ExpressionNode' object has no attribute 'split'`, for what version my django version is 1.7.10 – Grijesh Chauhan Feb 15 '16 at 11:11
  • if `progress` is a ForienKey of some other model, for instance let it be `Product` and it has a field `price`, how should be reformate it? `Task.objects.aggregate(total=Sum(F('progress.price') * F('estimated_days')))['total']` doesn't work – ANFAS PV Aug 27 '20 at 05:59
74

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.

sha256
  • 2,739
  • 1
  • 23
  • 32
  • 2
    Works well, thanks, but beware that the *field* kwarg is not documented and that I didn't find any test about it in the Django test suite. – Sébastien Trottier Jan 06 '14 at 02:39
  • what does the 'progress' field accomplish? I'm trying to figure out this code snippet, since it's exacly what I need – Maor Oct 07 '15 at 02:21
  • 1
    @Maor I think you mean the first parameter of `Sum`? Well, then as I mentioned `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` – sha256 Oct 07 '15 at 07:55
  • 1
    @sha256, So I guess you mean the first parameter of Sum should be the name of the field in whose type you want the result to be returned? In this case the result will be returned in the field progress' datatype? – Binoj David Apr 11 '16 at 14:19
  • @BinojDavid, exactly! – sha256 Apr 12 '16 at 06:53
35

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')))
Jasonw
  • 4,916
  • 7
  • 37
  • 45
Antstud
  • 609
  • 7
  • 3
  • When aggregation is made with different type fields, you need to use ExpressionWrapper. Please, take a look to this solution: https://stackoverflow.com/questions/55458958/how-to-aggregate-the-average-of-a-calculation-based-on-two-columns?answertab=votes#tab-top – serfer2 Oct 17 '19 at 16:09
3

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)
Community
  • 1
  • 1
dani herrera
  • 39,746
  • 4
  • 87
  • 153