Summarize Django Queryset where Amount falls into Specific Groups

Question

I'd like to group my transactions by the amount category that they fall into and sum those. I'd love to do this with 1 query, but I can't figure out what this kind of grouping is called. So I'm having a hard time finding any examples.

zero_to_twenty = Transactions.objects.filter(amount__lte=0, amount__gt=-20).aggregate(Sum("amount"))
twenty_to_fifty = Transactions.objects.filter(amount__lte=-20, amount__gt=-50).aggregate(Sum("amount"))
fifty_to_seventy_five = Transactions.objects.filter(amount__lte=-50, amount__gt=-75).aggregate(Sum("amount"))
seventy_five_to_one_hundred = Transactions.objects.filter(amount__lte=-75, amount__gt=-100).aggregate(
    Sum("amount")
)
one_hundred_plus = Transactions.objects.filter(amount__lte=-100).aggregate(Sum("amount"))

Obviously this is not ideal, but it gets me the correct data. Is it possible to do this with 1 query?

Answer 1

You may want to try to group Transactions into groups and then run a Sum for those groups.

Transactions.objects.filter(amount__lte=0).annotate(
    group=Case(
        When(Q(amount__lte=0, amount__gt=-20), then=Value("above_minus_20")),
        When(Q(amount__lte=-20, amount__gt=-50), then=Value("between_minus_20_minus_50")),
        When(Q(amount__lte=-50, amount__gt=-75), then=Value("between_minus_50_minues_75")),
        When(Q(amount__lte=-75, amount__gt=-100), then=Value("between_minus_75_minues_100")),
        When(Q(amount__lte=-100), then=Value("below_minues_100")),
        default=Value("no_group"),
    ),
).annotate(sum_group_amount=Window(expression=Sum("amount"), partition_by="group")).values(
    "group", "sum_group_amount"
).distinct()

It should return something along this lines

<QuerySet [
    {'group': 'above_minus_20', 'sum_group_amount': Decimal('1111.20')}, 
    {'group': 'between_minus_20_minus_50', 'sum_group_amount': Decimal('120.22')}
    ...
]>