CODEWITHSUNDEEP
pythonsqldjango
wjh
wjh

Reputation: 597

Django model group by more than 2 elements

I have a table that contains userids and their score. 

eg. userid rating
    123    1
    123    2
    456    1
    789    5
    123    1

What I would like to achieve is a summary table like this:

rating 1 : 32 ratings by 20 ppl       
       2 : 37 ratings by 15 ppl 
       etc..

The number of ppl should be distinct users who have given that rating.

I have gone through Django model aggregation methods and know how to use count etc. But I am not able to come up with something that will achieve this.

Of course i can retrieve the whole set and use a for loop in python to count but that wouldnt be efficient.

Thanks for any help.

Upvotes: 1

Views: 151

Answers (2)

okm
okm

Reputation: 23871

The sql you're looking for is like:

SELECT rating, COUNT(rating) AS rc, COUNT(DISTINCT userid) FROM rating_tbl GROUP BY rating ORDER BY rc DESC;

You haven't provided the model, and actually it's not easy to represent the SQL in Django ORM, IMO. The easiest way is to write it directly, ref the doc.

Upvotes: 1

Salil
Salil

Reputation: 9722

Maybe something like this:

MyTable.objects.annotate(Count('rating')).order_by('rating_count').annotate(Count('user_id'))

Upvotes: 0

Related Questions