Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django sum values of a column after 'group by' in another column

I found some solutions here and in the django documentation, but I could not manage to make one query work the way I wanted.

I have the following model:

class Inventory(models.Model):
    blindid = models.CharField(max_length=20)
    massug = models.IntegerField()

I want to count the number of Blind_ID and then sum the massug after they were grouped.

My currently Django ORM

samples = Inventory.objects.values('blindid', 'massug').annotate(aliquots=Count('blindid'), total=Sum('massug'))

It's not counting correctly (it shows only one), thus it 's not summing correctly. It seems it is only getting the first result... I tried to use Count('blindid', distinct=True) and Count('blindid', distinct=False) as well.

This is the query result using samples.query. Django is grouping by the two columns...

SELECT "inventory"."blindid", "inventory"."massug", COUNT("inventory"."blindid") AS "aliquots", SUM("inventory"."massug") AS "total" FROM "inventory" GROUP BY "inventory"."blindid", "inventory"."massug"

This should be the raw sql

SELECT blindid, 
       Count(blindid) AS aliquots,
       Sum(massug) AS total
       FROM inventory 
       GROUP BY blindid
like image 450
renno Avatar asked Sep 20 '16 13:09

renno


1 Answers

Try this:

samples = Inventory.objects.values('blindid').annotate(aliquots=Count('blindid'), total=Sum('massug'))
like image 73
Windsooon Avatar answered Oct 20 '22 04:10

Windsooon