5

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.

Saqib Ali
  • 11,931
  • 41
  • 133
  • 272

1 Answers1

11

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".

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Thanks! I noticed the `django.db.models` module has methods `Min`, `Max` and `Avg`. Is there any automated way to calculate the Median too? – Saqib Ali Feb 26 '15 at 03:50
  • @SaqibAli glad to help. `Median` is not a part of the aggregation framework. See more information and workarounds at: http://stackoverflow.com/questions/942620/missing-median-aggregate-function-in-django. – alecxe Feb 26 '15 at 03:54