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;