Opened 5 years ago

Closed 4 years ago

# Arithmetic operations on aggregates

Reported by: Owned by: Debanshu Kundu Database layer (models, ORM) master Normal aggregate, annotate Debanshu Kundu, jorgecarleitao@… Accepted yes yes yes yes no no

### Description

In many cases one may require to do simple arithmetic operations (+, -, *, /) on aggregated (with numbers or other aggregates).
Eg. For model `Xyz`:

```class Xyz(models.Model):
a = models.IntegerField()
b = models.IntegerField()
```

If I want to find percent ratio of sum of all `a` and sum of all `b`, I can write:

```Xyz.objects.aggregate(ratio_percent=Sum('a')/Sum('b')*100)
```

This type of functionality has been implemented in this commit https://github.com/debanshuk/django/commit/722f7a4a17870fe1ffb6c207153d1fca6388a401

More operations (like pow) can also be added.

### comment:3 follow-up:  4 Changed 5 years ago by wim@…

Hi debanshuk,

Wouldn't it be possible to make the same calculation outside of the aggregate? That seems cleaner to me.

Wim

### comment:4 in reply to:  3 Changed 5 years ago by Debanshu Kundu

Hi Wim,

In the example given in the description, it would. And I guess in case of aggregates it would always be possible to make the calculations out side aggregate.
But, in case of annotate it would not. Well, it would certainly be possible, but wouldn’t be cleaner, as one would have to do the calculations for each element in resultant query-set.

Eg. Let modified `Xyz` be:

```class Xyz(models.Model):
user = models.ForeignKey(User)

a = models.IntegerField()
b = models.IntegerField()
```

Now if I want to find percent ratio of sum of all `a` and sum of all `b` of a `user`, for each `user`. I may write:

```Xyz.objects.values('user').annotate(ratio_percent=Sum('a')/Sum('b')*100)
```

Currently, to do this (without writing raw SQL), one would have to do something like:

```result = Xyz.objects.values('user').annotate(total_a=Sum('a'), total_b=Sum('b'))
final_result = [{'user': r['user'], 'ratio_percent': r['total_a']/r['total_b']*100} for r in result]
```

Also, If someone want to aggregate over annotation results, like in the example if I want to find maximum of the percentages, I may do:

```Xyz.objects.values('user').annotate(ratio_percent=Sum('a')/Sum('b')*100).aggregate(Max('ratio_percent'))
```

Currently, I would have to do:

```result = Xyz.objects.values('user').annotate(total_a=Sum('a'), total_b=Sum('b'))
final_result = max(r['total_a']/r['total_b']*100 for r in result)
```

In the second one, max calculation in done in python. In the first one it is done in SQL itself.

Thanks,
Debanshu Kundu.

Version 2, edited 5 years ago by Debanshu Kundu (previous) (next) (diff)

### comment:5 Changed 5 years ago by Tim Graham

Duplicate of #14030?

### comment:6 Changed 5 years ago by Debanshu Kundu

Well...not exactly duplicate, as #14030 is for something like this:

```Xyz.objects.aggregate(Sum(F('a')/F('b')*100))
```

but I guess #14030 can be extended to implement functionality of this ticket. In case of `annotate` something like this can be done:

```Xyz.objects.values('user').annotate(total_a=Sum('a'), total_b=Sum('b')).values('user', ratio_percent=F('total_a')/F('total_b')*100)
```

But, in case of `aggregate` this wont work as `aggregate` returns `dict` (not `QuerySet`). So, I guess some changes are required for `aggregate`. Also, both functionalities (operations on aggregates and passing F objects to aggregates) can co-exist (as I think).

Last edited 5 years ago by Debanshu Kundu (previous) (diff)

### comment:7 Changed 5 years ago by Florian Apolloner

Needs documentation: set set set Unreviewed → Accepted

### comment:8 Changed 5 years ago by Debanshu Kundu

Owner: changed from nobody to Debanshu Kundu new → assigned

### comment:10 Changed 5 years ago by Debanshu Kundu

Did amendment in the commit in pull request. Updated some conditions in test-cases to make them more reliable.

Last edited 5 years ago by Debanshu Kundu (previous) (diff)

### comment:11 Changed 5 years ago by Debanshu Kundu

Owner: Debanshu Kundu deleted assigned → new