[Django]-Django: GROUP BY two values

by

31👍

✅

(Player.objects
 .values('player_type', 'team')
 .order_by()
 .annotate(Count('player_type'), Count('team'))
👤Amarghosh

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

Leave a comment