Sum up a field in the queryset in Django

Question

models.py

class QaCommission(models.Model):
    user = models.ForeignKey(PlUser, on_delete=models.CASCADE, blank=True, null=True, related_name='user_commission')
    ref = models.ForeignKey(PlUser, on_delete=models.CASCADE, blank=True, null=True, related_name='ref_commission')
    price = models.FloatField(blank=True, null=True)
    pct = models.FloatField(blank=True, null=True)
    commission = models.FloatField(blank=True, null=True)
    status = models.IntegerField(blank=True, null=True, default=0)

serializers.py

class QaCommissionSerializer(serializers.ModelSerializer):
    class Meta:
        model = QaCommission
        fields = '__all__'

views.py

class QaCommissionList(viewsets.ModelViewSet):
    queryset = QaCommission.objects.all()
    serializer_class = QaCommissionSerializer

If we are filtering ref=60 in this view, results show like this:

{
"count": 18,
"next": "http://127.0.0.1:8008/api/qacommission/?ref=60&page=2",
"previous": null,
"results": [
    {
        "id": 1,
        "price": 20.0,
        "pct": 0.1,
        "commission": 2.0,
        "status": 1,
        "user": 7,
        "ref": 60
    },
    {
        "id": 2,
        "price": 10.0,
        "pct": 0.1,
        "commission": 1.0,
        "status": 1,
        "user": 7,
        "ref": 60
    },
    ......
    ......
    ......
    {
        "id": 10,
        "price": 15.0,
        "pct": 0.1,
        "commission": 1.5,
        "status": 1,
        "user": 7,
        "ref": 60
    }
]
}

I wanna sum up all the "commission" field in the results and attach the sum to the original queryset (maybe next to "count":18), as shown above, there are 18 commission need to be count.

How could I implement this? Need your help, thanks!

`from django.db.models import Sum \n qs = QaCommission.objects.filter().aggregate(commission_sum=Sum('commission')) sum is stored in qs['commission_sum']` — Rohit Chopra, Jul 27 '18 at 09:14
Maybe this [https://stackoverflow.com/questions/31920853/aggregate-and-other-annotated-fields-in-django-rest-framework-serializers](https://stackoverflow.com/questions/31920853/aggregate-and-other-annotated-fields-in-django-rest-framework-serializers) will answer your question — dorintufar, Jul 27 '18 at 09:14
Adding a class method in model class may help you to sum up of your commision value of queryset result. — Projesh Bhoumik, Jul 27 '18 at 11:28

JPG · Accepted Answer · 2018-07-27T09:49:25.363

Try to override list() method of ModelViewset as,

class QaCommissionList(viewsets.ModelViewSet):
    queryset = QaCommission.objects.all()
    serializer_class = QaCommissionSerializer

    def list(self, request, *args, **kwargs):
        response = super().list(request, *args, **kwargs)
        response.data['sum'] = sum([data.get('commission', 0) for data in response.data['results']])
        return response

This answer sum-up the commision from particular page and displaying it

UPDATE

from django.db.models import Sum


class QaCommissionList(viewsets.ModelViewSet):
    queryset = QaCommission.objects.all()
    serializer_class = QaCommissionSerializer

    def list(self, request, *args, **kwargs):
        response = super().list(request, *args, **kwargs)
        if 'ref' in request.GET and request.GET['ref']:
            response.data['sum'] = QaCommission.objects.filter(ref=int(request.GET['ref'])
                                                               ).aggregate(sum=Sum('commission'))['sum']
        return response

The abouve answer will return the Whole sum of commission column w.r.t the filter (irrespective of the pagination)

Thanks @bruno for mentioning such a valid point

What's the point of having a SQL database if you don't use it ? — bruno desthuilliers, Jul 27 '18 at 09:35
awesome answer! I need the sum from all pages, but I also benefit from the current page one. Thanks! — mrhaoji, Jul 27 '18 at 09:55

Sum up a field in the queryset in Django

1 Answers1