CODEWITHSUNDEEP
djangodjango-orm
Ernst
Ernst

Reputation: 514

Django ORM get average of two concatenated columns

I have 2 models

class Order:
  ...

and also I have a model Trade

class Trade:
   x = FK(Order, related_name="x_set")
   y = FK(Order, related_name="y_set")
   price = Decimal

I need to calculate for each Order an average of all x_set__price values and y_set__price values.

I tried something like this, but it doesn't work

Order.objects.annotate(average_price=Avg("x_set__price", "y_set__price"))

Upvotes: 1

Views: 205

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476554

Avg does not work with two (or more) elements. You can however emulate this with:

from django.db.models import Count, DecimalField, Sum
from django.db.models.functions import Coalesce

Order.objects.annotate(
    average_price=ExpressionWrapper(
        (Coalesce(Sum('x_set__price'), Value(0)) + Coalesce(Sum('y_set__price')), Value(0)) /
        (Coalesce(Count('x_set__price'), Value(0)) + Coalesce(Count('y_set__price'), Value(0))),
        output_field=DecimalField(max_digits=5, decimal_places=2)
    )
)

we thus determine the Sum of the values, and divide thus by the total number of elements. The output_field specifies what the type of output is.

Upvotes: 2

Related Questions