CODEWITHSUNDEEP
djangodjango-ormdistinct-values
Redgren Grumbholdt
Redgren Grumbholdt

Reputation: 1230

Django - How to annotate count() of distinct values

I have the following model:

class Bank(model.Model):
    name: models.CharField
    ....

Using the following sample data:

╔══════════════╗
║ Row ID, Name ║
╠══════════════╣
║ 1, ABC       ║
║ 2, ABC       ║
║ 3, XYZ       ║
║ 4, MNO       ║
║ 5, ABC       ║
║ 6, DEF       ║
║ 7, DEF       ║
╚══════════════╝

I want to extract distinct bank names like so:

[('ABC', 3), ('XYZ', 1), ('MNO', 1), ('DEF', 2)]

I have tried using annotate and distict but the following error is being raised:

NotImplementedError: annotate() + distinct(fields) is not implemented.

I've also come accross the following question on SO:

Question 1

Which has answers on using models.Count('name', distinct=True) but it's returning duplicate values. How can I handle this using Django ORM?

Upvotes: 5

Views: 4260

Answers (2)

Redgren Grumbholdt
Redgren Grumbholdt

Reputation: 1230

Arakkal Abu's Answer worked for some rows, but was returning some duplicates. I have no idea why, but I added the following to his suggestion:

qs = Bank.objects.values('name').annotate(count=Count('id')).order_by('name').distinct()

Which made me realize that the exception raised said: distinct(fields) is not implemented, not distinct().

Also, on @ruddra's input on using Count('name') instead of Count('id'), I have not seen the difference between them. They all returned same results.

So the following worked too:

qs = Bank.objects.values('name').annotate(count=Count('name')).order_by('name').distinct()

Upvotes: 1

JPG
JPG

Reputation: 88429

You can GROUP BY query,

from django.db.models import Count

qs = Bank.objects.values('name').annotate(count=Count('name'))

and you will get the result as,

[{'name': 'BAR', 'count': 1}, {'name': 'FOO', 'count': 3}]

Upvotes: 7

Related Questions