How to aggregate sum of several previous aggregated values in django ORM

Question

In use: django 3.2.10, postgresql 13.4
I have next query set with aggregation function Count

queryset = Model.objects.all().aggregate(
    trues=Count('id', filter=Q(criteria=True)),
    falses=Count('id', filter=Q(criteria=False)),
)

What I want:

queryset = Model.objects.all().aggregate(
    trues=Count('id', filter=Q(criteria=True)),
    falses=Count('id', filter=Q(criteria=False)),
    total=trues+falses, <--------------THIS
)

How to do this?

score 0 · Answer 1 · answered Jul 29 '22 at 12:02

There is little thing you can do after aggregation, as it returns a python dict object.

I do understand your example here is not your real situation, as you can simply do

Model.objects.aggregate(
    total = (Count('id', filter=Q(criteria=True))
        + Count('id', filter=Q(criteria=False)))
)

What I want to say is Django provides .values().annotate() to achieve GROUP BY clause as in sql language.

Take your example here

queryset = Model.objects.values('criteria').annotate(count=Count('id'))

queryset here is still a 'QuerySet' object, and you can further modify the queryset like

queryset = queryset.aggregate(
    total=Sum('count')
)

Hopefully it helps.

score 0 · Answer 2 · answered Jul 29 '22 at 12:07

it seems you want the total number of false and true criteria so you can simply do as follow

queryset = Model.objects.all().filter(
           Q(criteria=True) | Q(criteria=False)).count()

or you can use (not recommended except you want to show something in the middle)

from django.db.models import Avg, Case, Count, F, Max, Min, Prefetch, Q, Sum, When

query = Model.objects.annotate(trues=Count('id',filter=Q(criteria=True)), 
falses=Count('id',filter=Q(criteria=False))).annotate(trues_false=F('trues')+F('falses')).aggregate(total=Sum('trues_false'))

How to aggregate sum of several previous aggregated values in django ORM

2 Answers2