Menu
I need to get aggregated value of two columns. So first multiple them together and then get theirs sum(). Code below naturally does not work, it is just for clarification.
Is it somehow possible or should I use raw SQL?
SomeModel.objects
.filter(**something)
.aggregate(Sum('one_column' * 'another_col'))
838
When specifying the field to be aggregated in an aggregate function, Django will allow you to use the same double underscore notation that is used when referring to related fields in filters. Django will then handle any table joins that are required to retrieve and aggregate the related value.
It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory. Instead, Django uses the F() object to generate an SQL expression that describes the required operation at the database level.
Appending the annotate() clause onto a QuerySet lets you add an attribute to each item in the QuerySet, like if you wanted to count the amount of articles in each category. However, sometimes you only want to count objects that match a certain condition, for example only counting articles that are published.
You don't need that much raw SQL using extra().
obj = SomeModel.objects.filter(**something).extra(
select = {'total': 'SUM(one_column * another_column)'},
)
As I answered here https://stackoverflow.com/a/36024089/4614802 the correct solution depends on django version.
.aggregate(Sum('field1', field="field1*field2"))
.aggregate(Sum(F('field1')*F('field2'))
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
