How to do this Group By Query in Django QuerySet?

Question

My Django Model:

class MyModel(models.Model):
    a = IntegerField()
    b = IntegerField()

I want to write a query set that answers the following question: "For each distinct value of a, what is the min, max and average value of b?"

How do I do it?

The SQL would look something like this:

SELECT a, min(b), max(b), avg(b) FROM MyModel group by a

But I can't figure out how to to it with the aggregate and annotate methods. All the examples in the docs show how to use those methods when you are averaging on the same variable that you are grouping on.

Answer 1

You can use the Django aggregation framework:

from django.db.models import Max, Min, Avg

MyModel.objects.values('a').annotate(Min('b'), Max('b'), Avg('b'))

The values('a') part here basically means "group by a".