Django annotating fields with null values

April 18, 2022

I have list of Demand objects that have allocated field that would either be null or have a name (denoting this demand’s allocation).

I use annotations to count allocated/unallocated numbers per team:

Demand.objects.filter(project=project).values('team').annotate(
            unallocated=Count('allocated', filter=Q(allocated__isnull=True)),
            allocated=Count('allocated', filter=Q(allocated__isnull=False))
        )

What’s weird is that the numbers for the allocated annotation come out right, but the numbers for the unallocated are always zero.

For instance:

list(Demand.objects.filter(project=project).values('allocated', 'team'))

With the following outcome:

[{'allocated': None, 'team': 'Design'},
{'allocated': None, 'team': 'Engineering'},
{'allocated': None, 'team': 'Engineering'},
{'allocated': None, 'team': 'Engineering'},
{'allocated': None, 'team': 'Delivery'},
{'allocated': None, 'team': 'Product'}]

but the annotations with have just this:

<QuerySet 
[{'team': 'Delivery', 'unallocated': 0, 'allocated': 0},
{'team': 'Design', 'unallocated': 0, 'allocated': 0},
{'team': 'Engineering', 'unallocated': 0, 'allocated': 0},
{'team': 'Product', 'unallocated': 0, 'allocated': 0}]>

Am I doing it wrong or it may be a bug?

>Solution :

That is because Count(…) ^[Django-doc] does not count NULL, that is how SQL specifies how a COUNT aggregate works: it does not consider NULL values (this is also the case for AVG for example). But you can instead count the primary key for example:

from django.db.models import Count, Q

Demand.objects.filter(project=project).values('team').annotate(
    unallocated=Count('pk', filter=Q(allocated=None)),
    allocated=Count('allocated', filter=Q(allocated__isnull=False))
).order_by('team')

Therefore, you can also simplify allocated to:

from django.db.models import Count, Q

Demand.objects.filter(project=project).values('team').annotate(
    unallocated=Count('pk', filter=Q(allocated=None)),
    allocated=Count('allocated')
).order_by('team')