Django Aggregation: highest sum in categories

byMR

January 29, 2022

Hy Guys

I was wondering how I can get the highest sum of one category.

So i have a model with my categories and a model with my costs which contains a cost category.

My models.py

from django.db import models

class CostCat(models.Model):
    name = models.CharField(max_length=50, default=None, null=True, blank=True)


class Cost(models.Model):
    name = models.CharField(max_length=50, default=None, null=True, blank=True)
    cost_category = models.ForeignKey(CostCat, on_delete=models.CASCADE)
    amount = models.DecimalField(max_digits=12, decimal_places=2,null=True, blank=True)

The result should be that I can show the category with the highest sum of costs.

I have been stuck on that for hours now. The closest i got was with my tests as shown below.

I can print out all the sums and its respective category but now I now need to find out how to get the category and the value of the highest sum. And i’m really in a spiral right now.

views.py

def metrics(request):

    tests = CostCat.objects.annotate(sum=Sum('cost__amount'))

    for test in tests:

        print(test, test.sum)

    return render(request, 'metrics.html', "topcostcat":test, "topcostcatsum":test.sum )

Much appreciated if you could me a bit out!

Thanks in advance

>Solution :

You can order by the cost and obtain the last one, so:

def metrics(request):
    topcat = CostCat.objects.annotate(sum=Sum('cost__amount')).latest('sum')
    return render(request, 'metrics.html', {'topcat': topcat})