Django equivalent of COUNT with GROUP BY

python sql django django-queryset django-aggregation
Imran picture Imran · May 9, 2009 · Viewed 22.2k times · Source

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?

Answer

Alex Koshelev picture Alex Koshelev · May 9, 2009

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.

Related questions

Getting the SQL from a Django QuerySet

How do I get the SQL that Django will use on the database from a QuerySet object? I'm trying to debug some strange behavior, but I'm not sure what queries are going to the database. Thanks for your help.

Read More
How to perform OR condition in django queryset?

I want to write a Django query equivalent to this SQL query: SELECT * from user where income >= 5000 or income is NULL. How to construct the Django queryset filter? User.objects.filter(income__gte=5000, income=0) This doesn't work, because it …

Read More
sql "LIKE" equivalent in django query

What is the equivalent of this SQL statement in django? SELECT * FROM table_name WHERE string LIKE pattern; How do I implement this in django? I tried result = table.objects.filter( pattern in string ) But that did not work. How …

Read More