[Answered ]-Handle divide by zero with aggregated fields in Annotate expression

by

1👍

Cast it to a FloatField:

from django.db.models import Count, F, FloatField, Q
from django.db.models.functions import Cast


opps.values(name=F('market__marketname')).annotate(
    opps=Count('id', filter=Q(datecreated__range=(start_date, end_date))),
    Won=Count(
        'id', filter=Q(winloss='Won', date_closed__range=(start_date, end_date))
    ),
    Lost=Count(
        'id', filter=Q(winloss='Lost', date_closed__range=(start_date, end_date))
    ),
    Concluded=F('Won') + F('Lost'),
    win_rate=Case(
        When(
            Concluded__gt=0,
            then=Cast('Won', output_field=FloatField())
            * 100
            / Cast('Concluded', output_field=FloatField()),
        ),
        default=0,
        output_field=FloatField(),
    ),
).order_by('name')

That being said, I don’t see why you do this at the database side: you have the amount of won and list Opportunitys, so you can just do that at the Python/Django level. Furthermore please do not use the queryset to generate serialized data: use a serializer.

👤willeM_ Van Onsem

Leave a comment