Sum based in a condition and group by month - Django query

user_11260440 0
Viewed 34
class Movimentacao(models.Model):
    entrada_saida = models.CharField(max_length=100)
    date = models.DateField()
    movimentacao = models.CharField(max_length=200)
    produto = models.CharField(max_length=300)
    instituicao = models.CharField(max_length=200)
    quantidade = models.IntegerField()
    preco_unitario = models.DecimalField(max_digits=19, decimal_places=2)
    valor_da_operacao = models.DecimalField(max_digits=19, decimal_places=2)

My goal is to sum valor_da_operacao for each month in date. Besides that, the produto field must be equal to "dividendo" (condition). How can I manage in only one Django query?

My expectation is to have a dict like this: {"dict": [["2022-Jan", 1.530], ["2022-Feb", 422], ["2022-Mar", 1.822]]}

Thanks!

django
1 Answers

You need to use Trunc function. it will reduce you dates to minimum of your choosing.(2022-08-07 10:40:45 TruncDay==> 2022-08-07 00:00:00)

from django.db.models import Sum, DateField
from django.db.models.functions import Trunc

movimentacao_per_month = Experiment.objects.annotate(new_date=Trunc(
'date', 'month', output_field=DateField())).values(
    'new_date').annotate(sum_of_valor_da_operacao=Sum('valor_da_operacao'))

this code will first add a field of new_date to all you objects and it will be truncated to month. and after that chooses this new field and then adds a sum to all the objects.

user_77307050
Related