Django Queryset annotate based on unique value

Question

I am trying to write a queryset operation that transforms the first table into the second table as efficiently as possible This is the criteria: For each name, how many unique schools are affiliated with it? Also, the exact names and schools are unknown beforehand.

Name	School
John	USC
John	USC
John	UCLA
Adam	UCSD
Adam	USC

Name	num_unique_schools
John	2
Adam	2

Answer 1

1- Using Values method:

The students will be grouped by name, so you will only get an annotated result for each unique student name.

2- Using Count Method with distinct argument:

This is counting a "school" based a grouped student name , also remove duplicated schools.

code:

from django.db import models
    
results = StudentSchool.objects.values('name').annotate(
    num_unique_schools = models.Count('school', distinct=True)
).order_by("name")

Test Case