[FIXED] Filtering on the count with the Django ORM

Issue

I have a query that's basically "count all the items of type X, and return the items that exist more than once, along with their counts". Right now I have this:

Item.objects.annotate(type_count=models.Count("type")).filter(type_count__gt=1).order_by("-type_count")

but it returns nothing (the count is 1 for all items). What am I doing wrong?

Ideally, it should get the following:

Type
----
1
1
2
3
3
3

and return:

Type, Count
-----------
1     2
3     3

Solution

In order to count the number of occurrences of each type, you have to group by the type field. In Django this is done by using values to get just that field. So, this should work:

Item.objects.values('group').annotate(
     type_count=models.Count("type")
).filter(type_count__gt=1).order_by("-type_count")

Answered By - Daniel Roseman