Nifty Django Feature: Counting on Multiple Columns

published by	Tim Schilling
in blog	Better Simple
published date	2026-08-19
original entry	Nifty Django Feature: Counting on Multiple Columns

This feature is about counting on multiple columns in the ORM. The Count expression currently only supports counting on a single column, which can be a bit restrictive. Let’s see how we can extend the ORM to do more.

Here are the models we’ll be working with:

from django.db import models
from django.db.models import Count

class Species(models.Model):
    name = models.CharField(max_length=100)

class Pet(models.Model):
    species = models.ForeignKey(Species, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)

class Vet(models.Model):
    name = models.CharField(max_length=100)

class Appointment(models.Model):
    vet = models.ForeignKey(Vet, on_delete=models.CASCADE)
    pet = models.ForeignKey(Pet, on_delete=models.CASCADE)

Let’s try to count the number of dogs that each vet has seen.

from django.db.models import Count

Appointment.objects.filter(
	pet__species__name="dog",
).values("pet__name", "vet__name")

# [{'pet__name': 'Buddy', 'vet__name': 'Dr. Smith'},
#  {'pet__name': 'Buddy', 'vet__name': 'Dr. Smith'},
#  {'pet__name': 'Buddy', 'vet__name': 'Dr. Jones'},
#  {'pet__name': 'Rex', 'vet__name': 'Dr. Smith'}]

Our data shows that Buddy visited Dr. Smith twice and Dr. Jones once, while Rex visited Dr. Smith. So we have four appointments for two pets across two vets, resulting in three unique pet-vet pairs.

Unfortunately trying to annotate a Count expression won’t work for us here.

Species.objects.filter(name="dog").annotate(
    count_by_appointment=Count("pet__appointment", distinct=True),
    count_by_vet=Count("pet__appointment__vet", distinct=True),
).values("count_by_appointment", "count_by_vet").first()

# {'count_by_appointment': 4, 'count_by_vet': 2}
# Unique (pet, vet) pairs = 3, but neither approach finds it.

Trying to count on appointment or vet alone won’t work for us. Counting on appointment will return 4 and counting by vet will return 2. The trouble is that Count only works on a single column, but we want to count on two.

CountSubquery: Count on distinct rows of data

To make this work, we can create a new Subquery class that can count rows from a nested query. This way Subquery produces one row per unique pair; and the outer COUNT(*) counts those rows.

from django.db.models import IntegerField, OuterRef, Subquery

class CountSubquery(Subquery):
    template = "(SELECT COUNT(*) FROM (%(subquery)s) _count)"
    output_field = IntegerField()

Species.objects.filter(pet__species__name="dog").annotate(
    count_pairs=CountSubquery(
        Appointment.objects.filter(pet__species=OuterRef("pk"))
        .values("pet", "vet")
        .distinct()
    ),
).values("count_pairs").first()

# {"count_pairs": 3}

While that is cool, the real feature here is Django’s expression system’s flexibility. Many of the expressions have a template that controls how it renders to SQL. This makes it straightforward to customize the ORM to generate the SQL that you want. While there are times to drop to raw SQL, the ORM’s API does a good job of providing the hooks to avoid it.

For more information take a look at the following:

If you have thoughts, comments, or questions, please let me know. You can set up a meeting with me, or find me on the Fediverse, Django Discord server or use email.