CODEWITHSUNDEEP
djangodjango-queryset
deepthought
deepthought

Reputation: 87

Django Queryset annotate based on unique value

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

Upvotes: 2

Views: 205

Answers (1)

Eng.Faris Alsmawi
Eng.Faris Alsmawi

Reputation: 111

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

enter image description here

Upvotes: 2

Related Questions