Group by month and year but include 0 counts also

Question

I have created a queryset which counts all the instances in a month and in a year. What I'd like it to do is also include the months that have 0 count. How do I do that even though there are no instances of the months in my database?

payments_per_month = PaymentRequest.objects
                     .annotate(year=Extract('arrival_date', 'year')).values('year')
                     .annotate(month=Extract('arrival_date', 'month')).annotate(count=Count('*'))
                     .values('year', 'month', 'count').order_by('-year', '-month', 'count')

Output is:

<QuerySet [{'year': 2023, 'month': 3, 'count': 8}, {'year': 2023, 'month': 2, 'count': 5}, 
           {'year': 2023, 'month': 1, 'count': 18}, {'year': 2022, 'month': 11, 'count': 2}, 
           {'year': 2022, 'month': 10, 'count': 1}, {'year': 2022, 'month': 8, 'count': 1}]> 

For example December(12) is missing but I'd like that to be in my queryset as: {'year': 2022, 'month': 12, 'count': 0}

Answer 1

To include months with zero count in your queryset, you can start by generating a list of all the months you want to include in the queryset. Then, you can annotate the queryset with this list of months, and finally, you can use conditional expressions to count the instances of each month.

Try the following:

from django.db.models import Case, When, Value, IntegerField

# Create a list of all the months
all_months = [
    {'month': 1}, {'month': 2}, {'month': 3}, {'month': 4},
    {'month': 5}, {'month': 6}, {'month': 7}, {'month': 8},
    {'month': 9}, {'month': 10}, {'month': 11}, {'month': 12},
]

# Annotate the queryset with the list of all months.

payments_per_month = PaymentRequest.objects \
    .annotate(year=Extract('arrival_date', 'year')) \
    .annotate(month=Value(all_months, output_field=JSONField())) \
    .values('year', 'month') \
    .annotate(count=Count('*')) \
    .order_by('-year', '-month')

# Use conditional expressions to count the instances of each month
payments_per_month = payments_per_month.annotate(
    count=Case(
        *[When(month__month=m['month'], then='count') for m in all_months],
        default=Value(0),
        output_field=IntegerField(),
    ),
)

# Extract the final queryset values
payments_per_month = payments_per_month.values('year', 'month__month', 'count').order_by('-year', '-month__month', 'count')

The first instance of payments_per_month stores the initial queryset that counts the instances in each month of the current and previous years, but does not include months with zero count.

The second instance of payments_per_month stores the final queryset that includes all months of the year, including months with zero count. This queryset is generated by annotating the initial queryset with a list of all months, and then using conditional expressions to count the instances of each month.

While the two instances of payments_per_month have the same name, they refer to different querysets at different points in the code. The first instance is overwritten by the second instance, so only the final queryset is stored in payments_per_month at the end of the code.