[Fixed]-Django, division between two annotate result won't calculate correctly

by

45👍

✅

With newer versions of Django, you can use the new Func object to cast the values to FloatFields or DecimalFields before the Sum.

from django.db.models.functions import Cast
from django.db.models import FloatField
ctr_monthly= Cast(Sum('click'), FloatField())/Cast(Sum('impression')), FloatField())

Even with an older version of Django, you might be able to just specify the output_field on the Sum before annotating ctr_monthly like so:

from django.db.models import F
def get_queryset(self):
    return googleData.objects.filter(
        account=self.account_name
    ).values('date').annotate(
        click_sum=Sum(
            'click',
            output_field=FloatField()
        ),
        impression_sum=Sum(
            'impression',
            output_field=FloatField()
        ),
        converted_click_sum=Sum('converted_click'),
        conversion_value_sum=Sum('conversion_value'),
        cost_sum=Sum('cost')
    ).annotate(
        ctr_monthly=F('click_sum') / F('impression_sum')
    ).order_by('-date')
👤Bobort

3👍

As far as I am aware, there isn’t a way to do this using the ORM.

The Sum() function returns the same field type as put into it (i.e. an IntegerField() will always return an Integer). You could use a function like ExpressionWrapper to force the output to be a float, but that won’t help in this case as it will be too late: the division of two integers will have been already returning another integer.

To solve your problem, remove the ctr_monthly section form your query, and create a simple template tag which converts the two numbers to floats and divide them.

Your template will then look like:
<td>{{ monthly_ctr(googleData.click, googleData.impression) | floatformat:2}} </td>

👤Jon Combe

1👍

My case is a bit different but it will help you to solve your problem.
In my case I want float value for percentage but I got integer in response. So I use Cast to get float value for numerator which solved my problem.

return category_qs.annotate(
            rankings_count=Count(
                f"user_{self.category}_rankings__user", distinct=True
            ),
            percentage=Case(
                When(
                    rankings_count__gt=0,
                    then=Cast(F("rankings_count") * 100, FloatField())
                    / users_count,
                ),
                default=Value(0.0),
                output_field=FloatField(),
            ),
        ).order_by("-percentage")
👤Soman Talha

Leave a comment