[FIXED] Django query: FOR EACH month AND name SUM values

Issue

What I need: for each month, I need to get the total sales (SUM) from John and Mary. There are other people in the database, but I only need info from both of them.

[
  ["19-Nov", "John", 511.97], ["19-Nov", "Mary", 0], 
  ["19-Dec", "John", 568.21], ["19-Dec", "Mary", 1542.08], 
  ["20-Jan", "John", 621.20], ["20-Jan", "Mary", 401.06], 
  ["20-Feb", "John", 621.39], ["20-Feb", "Mary", 0], 
  ["20-Mar", "John", 0], ["20-Mar", "Mary", 871.14], 
  ["20-Apr", "John", 604.25], ["20-Apr", "Mary", 653.34], 
  ["20-May", "John", 584.94], ["20-May", "Mary", 1218.43],
]

The date must be formatted in this "Year-Month" type in order (oldest to newest ones). The total sales, even if it's zero, must be printed.

My Django model:

class Sellers(models.Model):
    date = models.DateField()
    name = models.CharField(max_length=300)
    value = models.DecimalField(max_digits=19, decimal_places=2)

Is it possible to create just one Django query to retrieve data in this format?

Tks!

Solution

Something similar to this should work.

from django.db.models import F, Func, Value, CharField,Sum

Sellers.objects.filter(
    name__in=['Jhon','Mary']
).annotate(
    formatted_date=Func(
         F('date'),
        Value('YY-Mon'),
        function='to_char',
        output_field=CharField()
    )
).values(  
     'name','formatted_date'
).annotate(
    sales_sum=Sum('value')
).order_by('formatted_date')

The Postgre 'to_char' function may need to be changed to fit your DB.

Answered By - Eduardo Alejandro Leyva