[Solved]-Django – annotate() – Sum() of a column with filter on another column

by

20👍

✅

You could use conditional aggregation 

queryset = queryset.annotate(
    stock_in_sum = Sum(Case(When(stock__ttype='I', then=F('stock__quantity')), output_field=DecimalField(), default=0)),
    stock_out_sum = Sum(Case(When(stock__ttype='O', then=F('stock__quantity')), output_field=DecimalField(), default=0)))
)

To make the sums, and after that compute the balance with F() expression

queryset = queryset.annotate(balance_stock=F('opening_stock') + F('stock_in_sum') - F('stock_out_sum'))

You can also chain the different operation instead of multiple assignations:

queryset = queryset.prefetch_related(...).annotate(...).annotate(...)
👤Nadège

Leave a comment