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

Question

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

Answer 1

Try this:

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