Django ORM Query for Sum of positive and negative values Separately

Question

I have a column in a database, let's call it as dummy_column. It has values both positive and negative. I want to calculate the sum of all the positive numbers as well as the sum of all the negative numbers as well as the whole sum(positive+negative) i.e. I want the output in 3 separate values.

Calculating the sum of both can be done by using Sum('dummy_column')

Tried with Case and When, but can't understand what to put in the 'then' clause.

Saw this answer related, but I want to avoid the 'extra'. - 'Django queryset SUM positive and negative values'

Wasn't much to write the code, so didn't. Any help would be appreciated.

Answer 1

Process with three aggregates

You can use a Sum(..) over a Case like:

from django.db.models import Case, F, IntegerField, Sum, Value, When

agg = SomeModel.objects.aggregate(
    total=Sum('value'),
    total_pos=Sum(Case(
        When(value__gt=0, then=F('value')),
        default=Value(0),
        output_field=IntegerField()
    )),
    total_neg=Sum(Case(
        When(value__lt=0, then=F('value')),
        default=Value(0),
        output_field=IntegerField(),
    ))
)

(with SomeModel the model for which we want to calculate the aggregate, and value the field (here an IntegerField, that contains values we want to sum up).

The When(..) objects thus specify that we pass the 'value' value for rows where value__gt=0 (the value is greater than zero), or value__lt=0 (the value is less than zero).

This will result in a dictionary with three keys, for example:

# sample output
agg == {'total': 12, 'total_pos': 14, 'total_neg': -2}

You can then thus fetch the values like:

agg['total']  # 12

Process with two aggregates

Since we know however that agg['total'] == agg['total_pos'] + agg['total_neg'], we can only calculate one of the elements, and post process the dictionary, like:

from django.db.models import Case, F, IntegerField, Sum, Value, When

agg = SomeModel.objects.aggregate(
    total=Sum('value'),
    total_pos=Sum(Case(
        When(value__gt=0, then=F('value')),
        default=Value(0),
        output_field=IntegerField()
    ))
)

agg['total_neg'] = agg['total'] - agg['total_pos']

This might be more efficient, since we avoid inspecting each row an extra time (although this depends on how the database does indexing, etc.).