Django annotate and values ​​(): an extra field in 'group by' causes unexpected results

Question

I have to miss something obvious, since the behavior is not so expected for this simple requirement. Here is my model class:

class Encounter(models.Model):
    activity_type = models.CharField(max_length=2, 
                           choices=(('ip','ip'), ('op','op'), ('ae', 'ae')))
    cost = models.DecimalField(max_digits=8, decimal_places=2)

I want to find the total cost for each activity. My request:

>>> Encounter.objects.values('activity_type').annotate(Sum('cost'))

What gives:

>>> [{'cost__sum': Decimal("140.00"), 'activity_type': u'ip'}, 
     {'cost__sum': Decimal("100.00"), 'activity_type': u'op'}, 
     {'cost__sum': Decimal("0.00"), 'activity_type': u'ip'}]

In the result set, there are 2 'ip' types. This is due to the fact that it is not grouped only using activity_type, but using activity_type AND cost , which does not give the expected result. The generated SQL query for this:

SELECT "encounter_encounter"."activity_type", 
    SUM("encounter_encounter"."total_cost") AS "total_cost__sum" 
    FROM "encounter_encounter" 
    GROUP BY "encounter_encounter"."activity_type", 
             "encounter_encounter"."total_cost"        <<<< THIS MESSES THINGS
    ORDER BY "encounter_encounter"."total_cost" DESC

How can I make this request work as expected (and implies docs if I am not mistaken) and only do this group on activity_type?

Answer 1

@Skirmantas, order_by. , Meta, group by, SQL .

, order_by() reset :

>>> Encounter.objects.values('activity_type').annotate(Sum('cost')).order_by()