11

I need to do the following query in Django:

SELECT sum(T.width * T.height) as amount
FROM triangle T
WHERE T.type = 'normal'
GROUP BY S.color

How can I do this using your django ORM? I tried this:

Triangle.objects.filter(type='normal').\
                 extra(select={'total':'width*height'}).\
                 values('id', 'total').\
                 annotate(amount=Sum('total'))

but it does not work, the error I get is that TOTAL is not in the model. How can I fix it?

Cœur
  • 37,241
  • 25
  • 195
  • 267
rodrixd
  • 510
  • 2
  • 6
  • 15

1 Answers1

14

Here's what you can do:

Triangle.objects.filter(type="normal").values('color').annotate(amount=Sum('id', field="width * height")

This will produce the following query (I've simplified for readability):

SELECT color, sum(width * height) as amount
FROM triangle 
WHERE type = 'normal'
GROUP BY color

Note: I've assumed color is a field of Triangle model as other fields.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Is this an undocumented feature...? I don't see it anywhere on the Django docs Aggregation page. https://docs.djangoproject.com/en/1.8/topics/db/aggregation/ – tufelkinder Jun 08 '15 at 21:39
  • @tufelkinder it's being a while I've used django and the answer was posted long ago. After a quick look at the docs, I don't see that `field` keyword documented. Good catch, would be glad if you can find a documentation reference. Thanks! – alecxe Jun 09 '15 at 07:24
  • I tried to use this on Django 1.11, but the `field` parameter did not calculate as the example, the `amount` just filled by the value of `id` column. – oon arfiandwi Mar 15 '21 at 22:32