Getting Max count based on Vehid group by Houseid Django

Question

This is the image of the table and output required with details

So, I wrote a Django query

ModelClassName.objects.values('houseid','vehid').annotate(Count('vehid')).order_by('houseid')

This is giving me the count of each vehid but as I am not sure how to incorporate Max here, I am unable to get the right result. Any help is appreciable. Thanks.

With the current code, what are you getting as output? Can you past the queryset here? — Walucas, Mar 09 '18 at 19:32
This is not from the example I have written but this is the actual Queryset that I get — shelholmes221, Mar 09 '18 at 21:20
I am not seeing a solution with annotate itself, may I suggest some SQL alternative? Or a logic for this queryset? — Walucas, Mar 09 '18 at 22:43

score 0 · Answer 1 · answered Mar 10 '18 at 00:07

Create a view to get the counts:

SELECT HOUSEID, VEHID, COUNT(*) FROM TABLE GROUP BY HOUSEID, VEHID

Then query the View

Select Houseid, VEHID, COUNT from MYVIEW A where A.COUNT = (Select Max(B.count) FROM MYVIEW B WHERE A.HOUSEID = B.HOUSEID)

Then you can use Django SQL (https://docs.djangoproject.com/en/2.0/topics/db/sql/) to help you.

(test and let me know if that worked)

Getting Max count based on Vehid group by Houseid Django

1 Answers1