[Answered ]-Format count integer into percentage inside annotate in Djnago rest

by

1👍

✅

You should try

from django.db.models.functions import Coalesce

Destination.objects.all().annotate(
    total_packages=Count('packages'),                                                          
    cold_count_percent=Coalesce(100.0 * Count('packages',filter=Q(packages__place_type=1)) / Count('packages'), 0),                                                                   
    humid_count_percent=Coalesce(100.0 * Count('packages',filter=Q(packages__place_type=2)) / Count('packages'), 0),                                    
    hot_count_percent=Coalesce(100.0 * Count('packages',filter=Q(packages__place_type=3)) / Count('packages'), 0),                                    
)

Also if you need to handle the case when Count('packages')=0, you need something like this:

from django.db.models.functions import Coalesce

Destination.objects.all().annotate(
    total_packages=Count('packages'),                                                          
    cold_count_percent=Coalesce(div_zero(100.0 * Count('packages',filter=Q(packages__place_type=1)) / Count('packages'), 'total_packages'), 0),                                                                       
    humid_count_percent=Coalesce(div_zero(100.0 * Count('packages',filter=Q(packages__place_type=2)) / Count('packages'), 'total_packages'), 0),                                    
    hot_count_percent=Coalesce(div_zero(100.0 * Count('packages',filter=Q(packages__place_type=3)) / Count('packages'), 'total_packages'), 0),                                   
)

where div_zero is below:

from django.db.models import Case, Value, FloatField
from django.db.models.expressions import When

INFINITY = 1000000000000 
def div_zero(result, divisor):
    return Case(
        When(**{
            divisor: 0,
            'then': Value(INFINITY, FloatField()),
        }),
        default=result,
        output_field=FloatField(),
    )
👤Oleksii Tambovtsev

Leave a comment