Django ORM query GROUP BY multiple columns combined by MAX

Question

I am using Django with MySQL. I have a model similar to the following:

class MM(models.Model):
    a = models.IntegerField()
    b = models.IntegerField()
    c = models.DateTimeField(auto_now_add=True)

I have multiple rows that a is equal to b, and I want to perform the following SQL query:

SELECT a, b, MAX(c) AS max FROM MM GROUP BY b, a;

How can this be done with Django ORM? I have tried different approaches using annotations, but now luck so far.

Thanks a lot!

score 13 · Answer 1 · answered May 14 '13 at 16:07

13

I think you can do something like:

MM.objects.all().values('b', 'a').annotate(max=Max('c'))

Note that you need to import something to use Max: from django.db.models import Max

values('b', 'a') will give GROUP BY b, a and annotate(...) will compute the MAX in your query.

answered May 14 '13 at 16:07

Alex

198
1
10

3

FYI the `all()` is redundant and is not required. – mynameistechno Jun 23 '15 at 03:30
Just to formalise, here's the [documentation link](https://docs.djangoproject.com/en/3.0/topics/db/aggregation/#values) "...the original results are grouped according to the unique combinations of the fields specified in the values() clause." – Cornel Masson Jun 11 '20 at 12:01

Vikram Singh Chandel · Answer 2 · 2016-02-16T12:28:08.693

3

You can try this also

from django.db.models import Max

mm_list=MM.objects.all().values('b','a').annotate(max=Max('c'))
for mm in mm_list:
    a=mm['a']
    b=mm['b']
    max=mm['max']

edited Feb 16 '16 at 12:28

answered Feb 16 '16 at 11:36

Vikram Singh Chandel

1,290
2
17
36

score 1 · Answer 3 · answered Feb 15 '16 at 22:12

Sum a field group by two fields.

from django.db.models import Sum

SQL

select caja_tipo_id, tipo_movimiento, sum(monto) from config_caja group by caja_tipo_id, tipo_movimiento

Django

objs = Caja.objects.values('caja_tipo__nombre','tipo_movimiento').order_by().annotate(total=Sum('monto'))

Django ORM query GROUP BY multiple columns combined by MAX

3 Answers3

Linked