Menu
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?
746
Multiply two columns and then sum based on one condition with a useful feature. 1 Select Math from the Formula Type drop down list; 2 In the Choose a formula listbox, select SUMPRODUCT with criteria option; 3 Then, in the Arguments input section, select the Lookup_col, Lookup_value, Array 1 and Array 2 from the original table as you need.
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
In the Formulas Helper dialog box, do the following operations: 1 Select Math from the Formula Type drop down list; 2 In the Choose a formula listbox, select SUMPRODUCT with criteria option; 3 Then, in the Arguments input section, select the Lookup_col, Lookup_value, Array 1 and Array 2 from the original table as you need.
In Excel, most of us may suffer to multiply two columns and then add them up, of course, we can multiply each items and then sum them, but this will be troublesome if there are hundreds or thousands rows need to calculate. In Excel, there is a powerful function – SUMPRODUCT, with it, we can quickly multiply two columns and then sum them.
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() )) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
