Django: Query Group by Month with filling empty months

Trying to group by sales by month like below which works perfect

monthbased = any_queryset.annotate(sales_month=TruncMonth('BidDate')
    ).values('sales_month'
    ).annotate(sum=Sum(F('Quantity')*F('SellPrice'))
    )

My question is about the months with no sales. I am using this result in a bar chart and if any month does not have sales then those months will not appear in the chart I want to show empty months with zero values.

I did it like this

# convert queryset to list of dicts
monthbased_list = [{'sales_month': x['sales_month'], 'sum': x['sum']} for x in monthbased]
# fill empty year and months until utc now
now = datetime.utcnow()
for y in range(now.year-2, now.year+1):
    for m in range(1, 13):
        if not any(d['sales_month'] == datetime(y, m, 1) for d in monthbased_list):
            monthbased_list.append({'sales_month': datetime(y, m, 1), 'sum': 0})
# sort by year and month
monthbased_list = sorted(monthbased_list, key=lambda k: k['sales_month'])
# delete future months
monthbased_list = [x for x in monthbased_list if x['sales_month'] <= now]

Is there a better way to do it?

you can try to add a default value by Coalesce:

from django.db.models.functions import Coalesce
...

.annotate(sum=Coalesce(Sum(....), 0))

...
Back to Top