Django, division between two annotate result won't calculate correctly

Question

I'm trying to get a division between two annotate results in queryset. Impression is much larger than click, so I should get tenth decimal.

def get_queryset(self):
        return googleData.objects.filter(account=self.account_name).\
            values('date').\
            annotate(Sum('click'), Sum('impression'), Sum('converted_click'),
                     Sum('conversion_value'), Sum('cost'), Sum('conversion_value'), ctr_monthly= Sum('click')/Sum('impression')).\
            order_by('-date')

Trouble here:

ctr_monthly= Sum('click')/Sum('impression'))

In template i have:

<td>{{ googleData.ctr_monthly | floatformat:2}} </td>

And the result is 0.00. If I do ctr_final = click * impression, it generates correctly. Click & Impression are both integerfield.

I tried use the float(), gave me a syntax error.

The other question is: what's the best pratice to make a queryset like this? Is there any way that I can break it down to several short piece of code and make it more neat and readable ?

Thank you~

Answer 1

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')