Django equivalent of COUNT with GROUP BY

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?

From stackoverflow

• 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
  

  Alex Koshelev : This call will produce wrong `GROUP BY` clause - with all model fields included. But author asked only one field grouping.

  Ayman Hourieh : I was trying to give a general example and point to the documentation. This is why I said that my snippet needed tweaking. I've added more examples to my answer now. Thanks for the comment. :)

• 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 only player_type field into GROUP BY clause.
  • order_by() - for exclusion possible default ordering that can cause not needed fields inclusion in SELECT and GROUP BY.