Annotating an annotated field in Django

Viewed 36

I am trying to Count on a field that is generated by a previous Count in Django. Here is a simplified model:

class CountryUpdate(models.Model):
    country = models.CharField()
    date = models.DateTimeField()

There is a new entry every time some country is updated. Computing the number of updates per country is fairly documented:

CountryUpdate.objects.values("country").annotate(total=Count("country"))

What I would like to do now is count how many country has been updated X times.

Basically, if I had the following entries:

Country Date
Germany 2021
Germany 2022
France 2021
Italy 2021
Spain 2021

I would like the following output:

Number of updates Count
1 3
2 1

I tried annotating the previous queryset but it does not work.

value = (
    CountryUpdate.objects
    .values("country")
    .annotate(total=Count("country"))
    .values("total")
    .annotate(global_total=Count("total"))
)

This gives the error

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

This SQL query outputs exactly what is needed but I don't manage to put it in Django:

SELECT total, count(total)
from (SELECT "country_update"."country",
             COUNT("country_update"."country") AS "total"
      FROM "country_update"
      GROUP BY "country_update"."country") as t
group by total;
0 Answers
Related