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

Question

I must be missing something obvious, as the behavior is not as 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 type. My query is:

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

Which yields:

>>> [{'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' type encounters. This is because it is not grouped by only activity_type but by activity_type AND cost which does not give the intended result. The generated SQL query for this is:

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 query work as expected (and as implied by the docs if I am not getting it wrong) and make it only do a group by on activity_type?

Answer 1

As @Skirmantas correctly pointed, the problem was related to order_by. Although it is not explicitly stated in the query, the default ordering in the model's Meta class is added to the query, which is then added to the group by clause because SQL requires so.

The solution is either to remove the default ordering or add an empty order_by() to reset ordering:

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