How to calculate cumulative sum with django ORM?

Question

I'm trying to group_by() data based on dates and with every day I want to calculate Count on that day also the total count so far.

Sample output I'm getting:

[
    {
        "dates": "2022-11-07",
        "count": 1
    },
    {
        "dates": "2022-11-08",
        "count": 3
    },
    {
        "dates": "2022-11-09",
        "count": 33
    }
]

Sample output I'm trying to achieve:

[
    {
        "dates": "2022-11-07",
        "count": 1,
        "cumulative_count": 1
     },
    {
        "dates": "2022-11-08",
        "count": 3,
        "cumulative_count": 4
    },
    {
        "dates": "2022-11-09",
        "count": 33,
        "cumulative_count": 37
    }
]

Here's my query:

self.serializer_class.Meta.model.objects.all().annotate(dates=TruncDate("date__date")).values("dates").order_by("dates").annotate(count=Count("channel", distinct=True)).values("count", "dates")

How can I extend this query to get a cumulative sum as well?

Please provide enough code so others can better understand or reproduce the problem. — Сергей Кох, Nov 14 '22 at 13:10
Give this question a look: https://stackoverflow.com/questions/43517901/cumulative-running-sum-with-django-orm-and-postgresql — Jovan V, Nov 16 '22 at 14:28
Does this answer your question? [Cumulative (running) sum with django orm and postgresql](https://stackoverflow.com/questions/43517901/cumulative-running-sum-with-django-orm-and-postgresql) — Abdul Aziz Barkat, Nov 17 '22 at 05:31

Mahammadhusain kadiwala · Answer 1 · 2022-11-17T06:39:48.363

I tried to solve your problem like this

models.py

class Demo(models.Model):
    count =models.IntegerField()
    dates = models.DateField()

serializers.py

class DemoSerializer(serializers.ModelSerializer):
    
    class Meta:
        model = Demo
        fields = "__all__"

Views.py

class DemoAPI(APIView):
    def get(self, request, pk=None, format=None):
        data = Demo.objects.all()
        cumulative_count= 0

        # Normal Django ORM Queruset
        print('--------- Default Queryset Response ---------')
        for i in data:
            del i.__dict__['_state']
            print(i.__dict__)

        # Adding cumulative_count key in ORM Queryset   
        for i in data:
            cumulative_count += i.__dict__['count']
            i.__dict__['cumulative_count'] = cumulative_count

        # Updated Django ORM Queruset with cumulative_count 
        print('--------- Updated Queryset Response ---------')
        for i in data:
            # del i.__dict__['_state']
            print(i.__dict__)

Output before delete `_state` key from Queryset

#--------- Default Queryset Response --------- 
{'_state': <django.db.models.base.ModelState object at 0x000001A07002A680>, 'id': 1, 'count': 1, 'dates': datetime.date(2022, 11, 7)}
{'_state': <django.db.models.base.ModelState object at 0x000001A07002A5C0>, 'id': 2, 'count': 3, 'dates': datetime.date(2022, 11, 8)}
{'_state': <django.db.models.base.ModelState object at 0x000001A07002A7A0>, 'id': 3, 'count': 33, 'dates': datetime.date(2022, 11, 9)}

#--------- Updated Queryset Response --------- 
{'_state': <django.db.models.base.ModelState object at 0x000002DAB66E0AC0>, 'id': 1, 'count': 1, 'dates': datetime.date(2022, 11, 7), 'cumulative_count': 1}
{'_state': <django.db.models.base.ModelState object at 0x000002DAB66E0C10>, 'id': 2, 'count': 3, 'dates': datetime.date(2022, 11, 8), 'cumulative_count': 4}
{'_state': <django.db.models.base.ModelState object at 0x000002DAB66E0D60>, 'id': 3, 'count': 33, 'dates': datetime.date(2022, 11, 9), 'cumulative_count': 37}

Output after delete `_state` key from Queryset Added `cumulative_count` key in Queryset

#--------- Default Queryset Response ---------
{'id': 1, 'count': 1, 'dates': datetime.date(2022, 11, 7)}
{'id': 2, 'count': 3, 'dates': datetime.date(2022, 11, 8)}
{'id': 3, 'count': 33, 'dates': datetime.date(2022, 11, 9)}

#--------- Updated Queryset Response ---------
{'id': 1, 'count': 1, 'dates': datetime.date(2022, 11, 7), 'cumulative_count': 1}
{'id': 2, 'count': 3, 'dates': datetime.date(2022, 11, 8), 'cumulative_count': 4}
{'id': 3, 'count': 33, 'dates': datetime.date(2022, 11, 9), 'cumulative_count': 37}

Yeah I too used this way eventually. But I was trying to do it within my query using orm. Is there a way to do it with orm? — Hamza Shah, Nov 16 '22 at 22:14
yes. I did it with python, but I was wondering if there's a way we can do it with orm — Hamza Shah, Nov 17 '22 at 05:50

How to calculate cumulative sum with django ORM?

1 Answers1

models.py

serializers.py

Views.py

Output before delete _state key from Queryset

Output after delete _state key from Queryset Added cumulative_count key in Queryset

Output before delete `_state` key from Queryset

Output after delete `_state` key from Queryset Added `cumulative_count` key in Queryset