Django - Group by Count of Foreign Key

Question

I have two models like so:

models.py

class Subscription(...):
    is_active = models.BooleanField(...)

class Order(...):
    subscription = models.ForeignKey('Subscription', related_name='orders', ...)
    ...

I want to know:

1. How many subscriptions have x number of orders (i.e. 2 subscriptions with 1 order, 3 subscriptions with 2 orders, etc.)
2. The average number of orders across all of my subscriptions (i.e. 1.6 orders)

I can susccesfully annotate the number of orders to a subscription queryset, but I am having trouble grouping the queryset by like number_of_orders from there:

views.py

from django.db.models import Count

queryset = Subscription.objects.filter(is_active=True).annotate(number_of_orders=Count('orders')
queryset = queryset.values('number_of_orders')

Which gets me something like: <QuerySet [{'number_of_orders': 1}, {'number_of_orders': 2}, {'number_of_orders': 1}, {'number_of_orders': 2}, {'number_of_orders': 2}]>

I am looking for a final result that looks something like:

<QuerySet [{"number_of_orders" : 1, "subscriptions_count" : 2}, {"number_of_orders" : 2, "subscriptions_count" : 3}]>

I've tried:

queryset = queryset.order_by('number_of_orders').aggregate(subscriptions_count=Count('number_of_orders'))

which doesn't group my subscriptions correctly, and:

queryset = queryset.order_by('number_of_orders').annotate(subscriptions_count=Count('number_of_orders'))

which fails with this error:

Cannot compute Count('number_of_orders'): 'number_of_orders' is an aggregate

question from:https://stackoverflow.com/questions/65924531/django-group-by-count-of-foreign-key

Answer 1

Waitting for answers