5

I've got the following model manager that operates on a SalesRecord:

def by_variety_and_date(self, start_date, end_date):
    return self.model.objects.filter(
        date__range=(start_date, end_date)
    ).values(
        "variety"
    ).annotate(
        qty_applied=Sum('qty_applied'),
        margin=Avg('margin')
    )

What I'd really like is for the margin=Avg('margin') to return a weighted average, based on qty_applied. Is there a way to do this with Django's annotate/aggregate queries? I've been experimenting with stringing .aggregate() to the end of this, but I still want the average per variety as described by this queryset.

The model in this case looks like this:

class Sale(models.Model):
    margin = models.DecimalField(null=True, blank=True, decimal_places=2, max_digits=12)
    qty_applied = models.IntegerField(null=True, blank=True)
    variety = models.ForeignKey(Variety, null=True)
    totals = Totals()

EDIT

This is what I finally ended up with. It's a bit wonky, but it does the trick.

def by_variety_and_date(self, start_date, end_date):
    return self.model.objects.filter(
        date__range=(start_date, end_date)
    ).values(
        "variety"
    ).annotate(
        qty_applied=Sum('qty_applied'),
        profit=Sum('profit'),
        cogs=Sum('cogs'),
        sales=Sum('value'),
        margin=Sum(
            (F('qty_applied')*F('margin')), output_field=FloatField()
        ) / Sum(
            'qty_applied', output_field=FloatField())
    )

I used the F objects as @WTower suggested to multiply each object's margin by it's qty_applied, then wrapped the whole thing in a Sum and divided it by the Sum of the qty_applied of all objects in the group. Works like a charm!

Community
  • 1
  • 1
Adam Starrh
  • 6,428
  • 8
  • 50
  • 89

2 Answers2

5

You can use the F() expression

>>> from django.db.models import F, FloatField, Sum
>>> MyModel.objects.all().aggregate(
...    w_avg=Sum((F('a')*F('b'))/F('b'), output_field=FloatField()))

More on aggregation here.

Alternatively, you can use custom SQL (not recommended) or a custom method in the manager.

Wtower
  • 18,848
  • 11
  • 103
  • 80
3

@Wtower's answer is incorrect. The result is equivalent to Sum(F('a')).

We need a Sum() in the denominator for this to work.

The example here provides a clearer example: https://stackoverflow.com/a/68505423/1960509

>>> from django.db.models import F, FloatField, Sum
>>> MyModel.objects.all().aggregate(
...    w_avg=Sum(F('a')*F('b'))/Sum(F('b')))

This result may be an int or float depending on input data. To coerce it to a float, you can use FloatField on each of the Sum() calls or call float() on the result

twolfson
  • 71
  • 2