CODEWITHSUNDEEP
django
notanumber
notanumber

Reputation: 6529

Aggregate queryset to calculate percentage is always returning 0

I'm trying to write a Django queryset that aggregates the percentage of verified contracts in our system. I can't seem to get it to work though - I'm always getting '0' for the percentage, even though I know that it should be closer to 25%.

My current attempts follows:

In[1]: Contract.objects.aggregate(
    total=Count('pk'),
    verified=Count('pk', filter=Q(verified_date__isnull=False)
)
Out[1]: {'total': 4000, 'verified': 1000}

In[2]: Contract.objects.aggregate(
    percentage=Count('pk', filter=Q(verified_date__isnull=False)) / Count('pk')
)
Out[2]: {'percentage': 0}

In[3]: Contract.objects.aggregate(
    percentage=Count('pk', filter=Q(verified_date__isnull=False), output_field=DecimalField()) / Count('pk', output_field=DecimalField())
)
Out[3]: {'percentage': Decimal('0')}

Upvotes: 3

Views: 771

Answers (1)

dirkgroten
dirkgroten

Reputation: 20682

You need to make the values decimal before dividing them, just multiply by 1.0 to make sure of that:

from django.db.models import ExpressionWrapper, Count, Value, DecimalField

Contract.objects.aggregate(perc=ExpressionWrapper(
    Count('pk', filter=Q(verified_date__isnull=False)) * Value(1.0) 
    / (Count('pk') * Value(1.0)), 
    output_field=DecimalField()))

I use ExpressionWrapper to be able to set the output_field parameter on the aggregation value itself.

UPDATE: After looking at this issue based on the comment that casting with output_field should have worked, I've found that this works as well:

from django.db.models import Count, DecimalField
from django.db.models.functions import Cast

Contract.objects.aggregate(perc=
    Cast(
        Count('pk', filter=Q(verified_date__isnull=False)), 
        DecimalField(max_digits=30, decimal_places=4)
    ) 
    / Cast(
        Count('pk'), 
        DecimalField(max_digits=30, decimal_places=4)
    )
)

The issue mentioned above explains why output_field doesn't work. Seems to be a tbd for the future :-)

Upvotes: 2

Related Questions