CODEWITHSUNDEEP
django
user3610386
user3610386

Reputation: 109

Django how to group by multiple fields and sum

I am trying to perform multiple group_by's on my table with the fields same as the serializer below (last column is money)

class MoneySerializer(serializers.ModelSerializer):
money_sum = serializers.IntegerField()

class Meta:
    model =  my_models.Money
    fields = ('date', 'city', 'money_sum')

My Query in my viewset is as follows

items = my_models.Money.objects.values('date', 'city').annotate(money_sum=Sum('money'))

But with this, I'll have a group_by done for the city column, but I'll still have duplicates for the date field. I would like to group the rows such that it groups by all the same city values and all the same date values.

edit: my model

class Money(models.Model):
    date = models.DateField()
    city = models.models.CharField(max_length=200)
    money = models.DecimalField(decimal_places=2, max_digits=20)

Upvotes: 1

Views: 1974

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476624

You need to add an order_by [Django-doc] to force a GROUP BY here:

Money.objects.values('date', 'city').annotate(
    money_sum=Sum('money')
).order_by('date', 'city')

This will result in a QuerySet of dictionaries, like:

<QuerySet [
    {'date': date(2019, 1, 1), 'city': 'Berlin', 'money_sum': 1425.00},
    {'date': date(2019, 1, 1), 'city': 'Vienna', 'money_sum': 1302.00},
    {'date': date(2019, 2, 9), 'city': 'Madrid', 'money_sum': 1789.00},
    {'date': date(2019, 6, 29), 'city': 'Vienna', 'money_sum': 2019.00}
]>

Upvotes: 3

Related Questions