Pietro Pietro - 3 months ago 14
Python Question

Django aggregate Count only True values

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'))

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.

Comments