CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-orm
max
max

Reputation: 10454

Django - Averaging in a Group Query with Distinct values

I have a question very similar to How to group by AND aggregate with Django

here is my model:

class Author(models.Models):
    name = models.CharField(max_length=9)

class Book(models.Models):
    author = models.ForeignKey(Author)
    score = models.IntegerField()

here is what I want to do:

select distinct(author__name, AVG(score) 
    from book 
    where author__name in ('A, 'B', 'C') 
    group by author__name order by avg desc;

This is what I tried:

print Book.objects.filter(author__name__in=['A', 'B', 'C'])\
                .values_list('author__name', 'score')\
                .annotate(avg=Avg('score'))

[('A', 22, 22), ('A', 24, 24), ('B', 50, 50), ('B', 52, 52)]

but I want to see:

[('A', 23), ('B', 52)]

Is there a way to do this properly in the query (not in a post-processing code)?

Upvotes: 1

Views: 2075

Answers (1)

Anush Devendra
Anush Devendra

Reputation: 5475

You are trying to groupby author's name so remove the avg_depth in the values_list. annotate will add the Avg score/avg_depth for each author. You can do like:

result = Book.objects.filter(author__name__in=['A','B','C']).values_list('author__name').annotate(Avg('score'))

Here values_list is used to groupby author's name and Avg('score') will calculate the average score for each author which then would be annotated to each author using annotate()

Upvotes: 3

Related Questions