1

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)?

Community
  • 1
  • 1
max
  • 9,708
  • 15
  • 89
  • 144

1 Answers1

2

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()

Anush Devendra
  • 5,285
  • 1
  • 32
  • 24
  • While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – Bono Nov 22 '15 at 10:37
  • Thanks. My mistake was that I had two fields in the values_list. Also, I avg_depth was score but I did not notice it when I was making up the example from my real project. – max Nov 22 '15 at 17:45