Division between two annotations

February 18, 2022

I’m creating those two annotations as it follows:

cs = Champion.objects.all()

total_games = Match.objects.all().count()

cs = cs.annotate(
    picked_games=Count(
        expression='participants__match__id',
        filter=Q(participants__role='BOTTOM'),
        distinct=True
    ),
    total_games=Value(str(total_games), output_field=IntegerField())
)

And everthing’s alright until here. I fetch both the picked_games and total_games with correct results.

>>> cs.get(name='Jhin').picked_games
27544
>>> cs.get(name='Jhin').total_games
97410

However, if i try to divide one by another:

cs = cs.annotate(
    pick_rate=ExpressionWrapper(
        expression=F('picked_games') / F('total_games'),
        output_field=FloatField()
    )
)

This results on a 0:

>>> cs.get(name='Jhin').pick_rate
0.0

I don’t understand what’s problem here..

I can get the result if divide them externally, so why can’t i get the result on a different column for the whole queryset?

>>> cs.get(name='Jhin').picked_games / cs.get(name='Jhin').total_games
0.28319474386613286

>Solution :

You should cast the numerator (or denominator) to a float before making the division, otherwise the PostgreSQL database will use integer division and thus truncate towards zero. You thus can work with:

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

total_games = Match.objects.all().count()

Champion.objects.annotate(
    picked_games=Count(
        expression='participants__match__id',
        filter=Q(participants__role='BOTTOM'),
        distinct=True
    )
).annotate(
    pick_rate=Cast('picked_games', output_field=FloatField()) / total_games
)