python - Django Sum float values

Question

I have model in Django which have three fields. Float, and two CharFields. I need to sum the float fields by the other two.

models.py

class Hours(model.Model):
    hours_worked = models.FloatField()
    order_number = models.CharField()
    oper = models.CharField()

What I need to do is go through all data in DB and sum hours_worked for the same order_number+oper. So let say I have in db 4 records:

|hours_worked|order_number|oper|
|------------|------------|----|
|           4|         252|  10|
|           8|         320|  20|
|           8|         252|  10|
|           6|         252|  20|

And I need to return queryset of three objects like this:

|hours_worked|order_number|oper|
|------------|------------|----|
|          12|         252|  10|
|           8|         320|  20|
|           6|         252|  20|

Is there any easy way how to do it?

question from:https://stackoverflow.com/questions/65934526/django-sum-float-values

Answer 1

Here are two possible solutions (there are certainly others)

using django-group-by

from django_group_by import GroupByMixin

class GroupedHoursQuerySet(QuerySet, GroupByMixin):
    def select_related(self, *fields):
        try:
            return super().select_related(*fields)  # once grouping was applied, select_related will fail
        except TypeError:
            return self

class GroupedHours(Hours):
    objects = GroupedHoursQuerySet.as_manager()
    class Meta:
        proxy = True

and to query the data:

from django.contrib.postgres.aggregates import ArrayAgg, StringAgg
from django.db.models import Sum

qs = GroupedHours.objects.group_by('order_number', 'oper').annotate(
    hours_worked=Sum('hours_worked'),
    pks=ArrayAgg('id'),
    pk=StringAgg(Cast('id', output_field=TextField()), '-'),
).distinct()

The objects in the returned queryset have the same attributes as the original model but hours_worked contains the sum:

for gh in qs:
    print(f'Operator {gh.oper} worked {gh.hours_worked} on order {gh.order_number}')

Django's values()

You can of course use aggregate and annotate without the django-group-by module if you don't need Django model objects be just the actual numbers.

The following might also work (untested), refer to the documentation for more details:

from django.db.models import Sum

qs = Hours.objects.values('order_number', 'oper').annotate(
    worktime=Sum('hours_worked')

It might be necessary to add a .distinct().

This returns a dict:

for gh in qs:
    print(f'Operator {gh['oper']} worked {gh['hours_worked']} on order {gh['order_number']}')