[Django]-How to subtract two annotated columns on Django QuerySets?

by

21👍

✅

Actually,

c = c.annotate(variance=F('metric')-F('metric_prior'))

works as you would like it to starting with Django 1.8.

Moreover, you can also order by an expression, which means you can just use:

c = c.order_by(F('metric') - F('metric_prior'))

or even just:

c = c.order_by(Sum('results__metric') - Sum('results__metric_prior'))
👤maciek

2👍

Ticket is there for more than 4 years (by 2014), but it can be accomplished with a little .extra() query, like this:

items = MyModel.objects.extra(
    select = {'variance': 'SUM(relatedModel__someField) - SUM(relatedModel__someField)'},
)

Yes, it can be a bit unpredictable with different DBMS. But if you limit the syntax inside extra to very common SQL it should work more or less everywhere.

👤Ivan Anishchuk

Leave a comment