CODEWITHSUNDEEP
pythonsqldjangodjango-modelsgroup-by
rodrixd
rodrixd

Reputation: 510

How multiply and sum two columns with group by in django

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?

Upvotes: 11

Views: 6111

Answers (1)

alecxe
alecxe

Reputation: 473763

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.

Upvotes: 14

Related Questions