Annotate a sum of two fields multiplied

Question

I have three models, simplified for the example:

class Customer(models.Model):
    email = models.CharField(max_length=128)

class Order(models.Model):
    customer = models.ForeignKey(Customer)
    order_status = models.CharField(blank=True, max_length=256)

class Lineitem(models.Model):
    order = models.ForeignKey(Order)
    quantity = models.IntegerField(blank=True)
    price = models.DecimalField(max_digits=6, decimal_places=2)

I want to query the customers (possibly with a filter) and annotate the total they have spent (that is, the sum over (price * quantity)

I have tried:
Customer.objects.filter(something).annotate(total_spent=Sum(F('order__lineitem__quantity') * F('order__lineitem__price')))

It would appear that Sum() cannot be used with F() expressions. Is there another way to do this?

Possible duplicate of [Django Aggregation: Summation of Multiplication of two fields](http://stackoverflow.com/questions/12165636/django-aggregation-summation-of-multiplication-of-two-fields) — Louis, Jun 27 '16 at 10:43

score 31 · Answer 1 · edited Jan 01 '21 at 14:50

Maybe you don't need this answer now, but if you read the documentation about Sum expression , you need to declare the output_field, like this:

Customer.objects.filter(something)
                .annotate(total_spent=Sum(
                    F('order__lineitem__quantity') * 
                    F('order__lineitem__price'),   
                    output_field=models.FloatField()
                ))

murgatroid99 · Answer 2 · 2011-08-24T16:38:59.223

2

You could try using a property in the LineItem model:

class Lineitem(models.Model):
    order = models.ForeignKey(Order)
    quantity = models.IntegerField(blank=True)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    def _get_total(self):
        return quantity * price
    total = property(_get_total)

Then I think you can annotate with the total amount spent using

Customer.objects.filter(something).annotate(total_spent=Sum('order__lineitem__total'))

I don't know how the efficiency of this method relates to others, but it is more Pythonic/Django-y than the alternative, which is to write the entire SQL query by hand as in

Customer.objects.raw("SELECT ... from <customer_table_name> where ...")

edited Aug 24 '11 at 16:38

answered Aug 24 '11 at 15:07

murgatroid99

19,007
10
60
95

I have tried this and it isn't working, I'm getting the `Cannot resolve keyword into field` error. :-( – Berislav Lopac May 19 '12 at 08:18
OK, from the comment on the other answer it looks like this cannot be done with annotate on a property. – murgatroid99 May 19 '12 at 20:43
The Django ORM generates queries that perform calculations through SQL, and therefore, it's not possible to include Python-defined properties in methods as part of a query. You can apply a list-comprehension post-query, but this isn't the same thing, as Justin Hamade noted. – acjay Nov 23 '12 at 14:47

score 1 · Answer 3 · answered Oct 16 '13 at 21:56

1

You'll probably need to roll out your own custom aggregator. You can find a simple walk through of a GROUP_CONCAT example that should get you started here: http://harkablog.com/inside-the-django-orm-aggregates.html

answered Oct 16 '13 at 21:56

Chad

1,794
1
17
30

Grant · Answer 4 · 2009-04-30T18:08:11.720

1

Have you looked at using the .extra() method?

See the Django QuerySet API.

edited Apr 30 '09 at 18:08

answered Apr 30 '09 at 17:54

Grant

2,838
1
19
17

3

I have. It works, but I'm trying to avoid it for two reasons: First, it uses a per-row subquery instead of a join, which may scale badly for some database backends. Second, it doesn't work with filter() on the extra field, so it can't be combined procedurally with other Q objects – Sam Apr 30 '09 at 19:12

score 0 · Answer 5 · edited May 23 '17 at 11:53

0

Similar to: https://stackoverflow.com/a/19888120/1344647

from django.db.models import Sum

q = Task.objects.filter(your-filter-here).annotate(total=Sum('progress', field="progress*estimated_days"))

Edited: Thanks to @Max, using annotate instead aggregated.

edited May 23 '17 at 11:53

Community

1
1

answered Jun 18 '15 at 02:51

Ali

559
1
5
13

1

you in a right way: but instead of aggregate use annotate and remove ['total'] In result: total = Task.objects.filter(your-filter-here).annotate(total=Sum('progress', field="progress*estimated_days")). Remake example to be a positive – Max Mar 15 '16 at 12:21

score 0 · Answer 6 · edited May 23 '17 at 12:00

I just ran into this and I don't think that annotate and will work with a property, see Django - Can you use property as the field in an aggregation function?

Here is what I did.

class Customer(models.Model):
    email = models.CharField(max_length=128)

class Order(models.Model):
    customer = models.ForeignKey(Customer)
    order_status = models.CharField(blank=True, max_length=256)

class Lineitem(models.Model):
    order = models.ForeignKey(Order)
    quantity = models.IntegerField(blank=True)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    @property
    def total(self):
        return self.quantity * self.price

Then use sum and a list comprehension:

sum([li.total for li in  LineItem.objects.filter(order__customer=some_customer).filter(somefilter)])

that does not work because properties are calculated after the query — toto_tico, May 15 '12 at 15:28
This option works but you are doing the sum in python not sql which means it will be a lot slower as it gets big. — Jorge Vargas, May 30 '12 at 16:49

Annotate a sum of two fields multiplied

6 Answers6

Linked