27

I would basically like to do the same as this question, but grouping by combinations of two values, rather than just one:

SELECT player_type, team, COUNT(*)
FROM players
GROUP BY player_type, team;

Does anyone know whether, and how, this is possible in Django? I'm using 1.2.

dfrankow
  • 20,191
  • 41
  • 152
  • 214
AP257
  • 89,519
  • 86
  • 202
  • 261

2 Answers2

31
(Player.objects
 .values('player_type', 'team')
 .order_by()
 .annotate(Count('player_type'), Count('team'))
dfrankow
  • 20,191
  • 41
  • 152
  • 214
Amarghosh
  • 58,710
  • 11
  • 92
  • 121
  • 21
    This answer would actually return a query with two calls to COUNT, both player_type and team. As Django does not support the asterisk parameter for the count, COUNT(*) can be achieved by using a non-null field. The PK is a good candidate for this. thus using Count('pk') would be the right answer... – Vajk Hermecz Jan 25 '13 at 09:30
0

The most straightforward way to do this is to count the id field of the objects and trigger values() and annotate() into doing the grouping.

Assuming the OP's original query, with a Django (v4.2) datamodel something like this:

# home/models.py
class Player(models.Model):
    player_type = models.CharField(max_length=50)
    team = models.CharField(max_length=50)

Executing:

Player.objects.values('player_type', 'team').annotate(Count('id'))

Will produce the desired output, with the counts in a new field id__count.

It's a good idea to sanity-check the .query property on the QuerySet. In the previous example, something similar to this is generated:

-- print(Player.objects.values('player_type', 'team').annotate(Count('id')).query)

SELECT "home_player"."player_type"
     , "home_player"."team"
     , COUNT("home_player"."id") AS "id__count"
FROM "home_player"
GROUP BY "home_player"."player_type", "home_player"."team"
x0lani
  • 41
  • 4