[Fixed]-Django Aggregate- Division with Zero Values

by

24👍

✅

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.

👤Saeed Arabi

4👍

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(),
    ),
)
👤msonsona

1👍

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.

👤denvaar

Leave a comment