Django Aggregate- Division with Zero Values

Question

I am using Django's aggregate query expression to total some values. The final value is a division expression that may sometimes feature zero as a denominator. I need a way to escape if this is the case, so that it simply returns 0.

I've tried the following, as I've been using something similar my annotate expressions:

from django.db.models import Sum, F, FloatField, Case, When

def for_period(self, start_date, end_date):
    return self.model.objects.filter(
        date__range=(start_date, end_date)
    ).aggregate(
        sales=Sum(F("value")),
        purchase_cogs=Sum(F('purchase_cogs')),
        direct_cogs=Sum(F("direct_cogs")),
        profit=Sum(F('profit'))
    ).aggregate(
        margin=Case(
            When(sales=0, then=0),
            default=(Sum(F('profit')) / Sum(F('value')))*100
        )
    )

However, it obviously doesn't work, because as the error says:

'dict' object has no attribute 'aggregate'

What is the proper way to handle this?

Answer 1

I've made it work (in Django 2.0) with:

from django.db.models import Case, F, FloatField, Sum, When

aggr_results = models.Result.objects.aggregate(
    at_total_units=Sum(F("total_units")),
    ag_pct_units_sold=Case(
        When(at_total_units=0, then=0),
        default=Sum("sold_units") / (1.0 * Sum("total_units")) * 100,
        output_field=FloatField(),
    ),
)

Answer 2

This will obviously not work; because aggregate returns a dictionary, not a QuerySet (see the docs), so you can't chain two aggregate calls together.

I think using annotate will solve your issue. annotate is almost identical to aggregate, except in that it returns a QuerySet with the results saved as attributes rather than return a dictionary. The result is that you can chain annotate calls, or even call annotate then aggregate.

So I believe something like:

return self.model.objects.filter(
    date__range=(start_date, end_date)
).annotate(  # call `annotate`
    sales=Sum(F("value")),
    purchase_cogs=Sum(F('purchase_cogs')),
    direct_cogs=Sum(F("direct_cogs")),
    profit=Sum(F('profit'))
).aggregate(  # then `aggregate`
    margin=Case(
        When(sales=0, then=0),
        default=(Sum(F('profit')) / Sum(F('value')))*100
    )
)

should work.

Hope this helps.

Answer 3

You can't chain together aggregate statements like that. The docs say:

aggregate() is a terminal clause for a QuerySet that, when invoked, returns a dictionary of name-value pairs.

It returns a python dict, so you'll need to figure out a way to modify your query to do it all at once. You might be able to replace the first call to aggregate with annotate instead, as it returns a queryset:

Unlike aggregate(), annotate() is not a terminal clause. The output of the annotate() clause is a QuerySet

As for the division by 0 possibility, you could wrap your code in a try catch block, watching for ZeroDivisionError.