Why annotate generates duplicate entries?

Question

Given the following code:

payments = Payment.objects.filter(
        customer=self.customer,
        created_at__gte=kwargs['start_date'],
        created_at__lte=kwargs['end_date']
    ).order_by('-date')

balance = payments.values('currency').annotate(Sum('amount'))

> print(balance)
> [{'amount__sum': Decimal('0.00'), 'currency': 'USD'},
   {'amount__sum': Decimal('0.00'), 'currency': 'USD'},
   {'amount__sum': Decimal('9000.00'), 'currency': 'SEK'},
   {'amount__sum': Decimal('45000.00'), 'currency': 'EUR'},
   {'amount__sum': Decimal('11385.00'), 'currency': 'SEK'}]

List of payments for this customer:

> print(payments)
> ('-1487.50', 'USD')
('1487.50', 'USD')
('-3663.72', 'USD')
('3663.72', 'USD')
('15000.00', 'EUR')
('9000.00', 'SEK')
('30000.00', 'EUR')
('9865.00', 'SEK')
('1520.00', 'SEK')

If I use aggregate, I get the Sum, but for all currencies and that's not what I want. I must be able to split into currencies.

{'amount__sum': Decimal('65385.00')}

I am trying to extract the payments by customer grouping and summing by currencies. However what happens is that it doesn't Sum some of the values instead duplicating them. Any ideas?

Answer 1

It is the order_by that causes this. Fields mentioned in order_by will implicitly be part of the group otherwise defined as the fields mentioned in values according to the docs on aggregation and order_by.

See if

Payment.objects.filter(
        customer=self.customer,
        created_at__gte=kwargs['start_date'],
        created_at__lte=kwargs['end_date']
    ).values('currency').annotate(Sum('amount'))

won't give you exactly the answer you are looking for.

This will also be caused by a default ordering set on Payment.Meta.ordering. If you did that you will need to cancel out the ordering explicitly like so:

Payment.objects.filter(
        customer=self.customer,
        created_at__gte=kwargs['start_date'],
        created_at__lte=kwargs['end_date']
    ).order_by().values('currency').annotate(Sum('amount'))