I am trying to recreate this SQL statement using Django’s query builder:
CASE
WHEN sum(imps) = 0 THEN NULL
ELSE SUM(total_expenses) / (sum(imps) / 1000)
END as "cpm"
I have tried:
MyModel.objects.annotate(
cpm=Case(
When(
Sum("imps")>0,
then=Sum("total_expenses") / Sum("imps"),
),
default=0,
output_field=FloatField(),
)
)
However it doesn’t work because of this TypeError '>' not supported between instances of 'Sum' and 'int'.
If there was a field lookup that enabled me to do total_imps__sum__gt=0, I am sure that would solve the problem.
Note:
Adding Sum("imps") as an annotation (total_imps=Sum("imps")) and doing total_imps__gt=0 works but it is not an ideal solution as I would have to create multiple annotations for fields I won’t need.
3
Use NullIf instead
MyModel.objects.annotate(
Cast(
Sum("total_expenses") /1.0/ NullIf(Sum("imps"), 0)
output_field=FloatField()
),
)
You can use alias() in your original query to omit adding additional annotation