Django aggregate Count only True values

Question

I'm using aggregate to get the count of a column of booleans. I want the number of True values.

DJANGO CODE:

count = Model.objects.filter(id=pk).aggregate(bool_col=Count('my_bool_col')

This returns the count of all rows.

SQL QUERY SHOULD BE:

SELECT count(CASE WHEN my_bool_col THEN 1 ELSE null END) FROM <table_name>

Here is my actual code:

stats = Team.objects.filter(id=team.id).aggregate(
    goals=Sum('statistics__goals'),
    assists=Sum('statistics__assists'),
    min_penalty=Sum('statistics__minutes_of_penalty'),
    balance=Sum('statistics__balance'),
    gwg=Count('statistics__gwg'),
    gk_goals_avg=Sum('statistics__gk_goals_avg'),
    gk_shutout=Count('statistics__gk_shutout'),
    points=Sum('statistics__points'),
)

Thanks to Peter DeGlopper suggestion to use django-aggregate-if

Here is the solution:

from django.db.models import Sum
from django.db.models import Q
from aggregate_if import Count

stats = Team.objects.filter(id=team.id).aggregate(
    goals=Sum('statistics__goals'),
    assists=Sum('statistics__assists'),
    balance=Sum('statistics__balance'),
    min_penalty=Sum('statistics__minutes_of_penalty'),
    gwg=Count('statistics__gwg', only=Q(statistics__gwg=True)),
    gk_goals_avg=Sum('statistics__gk_goals_avg'),
    gk_shutout=Count('statistics__gk_shutout', only=Q(statistics__gk_shutout=True)),
    points=Sum('statistics__points'),
)

Littles

Why is this not just SELECT count(*) FROM <table name> WHERE my_bool_col = 1? There's a simple expression for that query in Django, but either I don't understand what you're trying to do or you're doing this an unnecessarily hard way in SQL. – Fiacre

I added another example with my actual code to better explain – Littles

I am not sure that this will work, but have you tried whether the API allows you to call Sum on a BooleanField? It's kind of a hack, but since the DB representation is either 1 or 0 the math works out right. Failing that, this third-party library looks relevant: pypi.python.org/pypi/django-aggregate-if – Fiacre

Sum doesn't work. I'll give it a try, thanks. – Littles

Answer 1

Update:

Since Django 1.10 you can:

from django.db.models import Count, Case, When
query_set.aggregate(
    bool_col=Count(
        Case(When(my_bool_col=True, then=Value(1)))
    )
)

Read about the Conditional Expression classes

Old answer.

It seems what you want to do is some kind of "Conditional aggregation". Right now Aggregation functions do not support lookups like filter or exclude: fieldname__lt, fieldname__gt, ...

So you can try this:

django-aggregate-if

Description taken from the official page.

Conditional aggregates for Django queries, just like the famous SumIf and CountIf in Excel.

You can also first annotate the desired value for each team, I mean count for each team the ammount of True in the field you are interested. And then do all the aggregation you want to do.

Shapely

While appropriate for the time of the query, instead look at the built-in conditional aggregation functionality of Django. Django-aggregate-if hasn't been updated, presumably due to the functionality being rolled into Django. (Presumably since there is no indication of why it hasn't been updated.) – Taut

@BenjaminSchollnick, That's true, Answer updated. Thanks. – Shapely

Count() seems to count non-null values so that Value(0) counts as well as Value(1) or Value(42)... Use default=Value(None) instead to properly count using Count(). Or simple remove the default= as it seems to default to None (=null) – Criticize

Answer 2

Updated for Django 1.10. You can perform conditional aggregation now:

from django.db.models import Count, Case, When
query_set.aggregate(bool_col=Count(Case(When(my_bool_col=True, then=1))))

More information at:

• https://docs.djangoproject.com/en/1.11/ref/models/conditional-expressions/#case

Marlette

This answer is more up to date and a better option over the 3rd party django-aggregate-if module – Okajima

Extremely helpful, and significantly better than a 3rd party plugin/library that hasn't been updated to work past django 1.08. – Taut

@Okajima Thanks for pointing out my answer was deprecated, I've updated the answer so is still being the answer for those still working with old Django versions and those working with modern Django versions. Thanks. – Shapely

then=1 without a default is correct here. I ran into issues with then=Value(1) and default = Value(0) where the Count() call counted all values, so I'd get a Count() of 87 when I really only had 76 Trues. – Bollard

Answer 3

Another Solution for count Bool is:

from django.db.models import Sum, IntegerField
from django.db.models.functions import Cast

Model.objects.filter(id=pk).annotate(bool_col=Sum(Cast('my_bool_col', IntegerField())))

Just convert False to 0 and True to 1, and then just Sum

Tombola

Answer 4

This worked for me:

from django.db.models import Avg, Sum, Count, Q 

aggregated_values = queryset.aggregate(
        avg_length=Avg('field_name'),
        total_reserved=Count('field_name', filter=Q(field_name=True)),
        total_not_reserved=Count('field_name', filter=Q(field_name=False))
    )

Diastrophism