Django equivalent of COUNT with GROUP BY
Solution 1
If you are using Django 1.1 beta (trunk):
Player.objects.values('player_type').order_by().annotate(Count('player_type'))
-
values('player_type')
- for inclusion onlyplayer_type
field intoGROUP BY
clause. -
order_by()
- for exclusion possible default ordering that can cause not needed fields inclusion inSELECT
andGROUP BY
.
Solution 2
Django 1.1 does support aggregation methods like count. You can find the full documentation here.
To answer your question, you can use something along the lines of:
from django.db.models import Count
q = Player.objects.annotate(Count('games'))
print q[0]
print q[0].games__count
This will need slight tweaking depending on your actual model.
Edit: The above snippet generates aggregations on a per-object basis. If you want aggregation on a particular field in the model, you can use the values
method:
from django.db.models import Count
q = Player.objects.values('playertype').annotate(Count('games')).order_by()
print q[0]
print q[0].games__count
order_by()
is needed because fields that are in the default ordering are automatically selected even if they are not explicitly passed to values()
. This call to order_by()
clears any ordering and makes the query behave as expected.
Also, if you want to count the field that is used for grouping (equivalent to COUNT(*)
), you can use:
from django.db.models import Count
q = Player.objects.values('playertype').annotate(Count('playertype')).order_by()
print q[0]
print q[0].playertype__count
Imran
Updated on September 06, 2020Comments
-
Imran over 3 years
I know Django 1.1 has some new aggregation methods. However I couldn't figure out equivalent of the following query:
SELECT player_type, COUNT(*) FROM players GROUP BY player_type;
Is it possible with Django 1.1's Model Query API or should I just use plain SQL?