Django annotate count with a distinct field

Question

I have two models defined loosely like this:

class InformationUnit(models.Model):
    username = models.CharField(max_length=255)
    project = models.ForeignKey('Project')
    ...

class Project(models.Model):
    name = models.CharField(max_length=255)

Now, in a view, I want to annotate all the InformationUnits that belong to a project, so I do this:

p = Project.objects.all().annotate(Count('informationunit')

which works just ok.

Furthermore, I want to know, in each project, how many distinct usernames participate. That is, count how many distinct usernames are there in the InformationUnits that compose one project. I have tried the following, but it simply counts the number of InformationUnit, regardless of the username:

p = Project.objects.all().annotate(Count('informationunit__username')

Note that username is not an object, it is a string. Is there a clean way to do this or should I create a more complicated code based on loops and spaghetti code :P

Thanks a lot!

spencer nelson · Accepted Answer · 2012-10-30 18:26:11Z

221

Count can take a distinct argument, like so:

p = Project.objects.all().annotate(Count('informationunit__username', 
                                         distinct=True))

This doesn't seem to be documented, but you can find it in the source for Count.

answered Oct 30, 2012 at 18:26

spencer nelson

4,5833 gold badges26 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fenomenoxp Over a year ago

It worked! I had found this somewhere and even tried it (actually it was the last thing I did before asking the question here). In the beginning it didnt work, but tried it again today after restarting the development server and it did the trick :P Thanks a lot!

Scott Stafford Over a year ago

It is documented (now, anyway): docs.djangoproject.com/en/dev/ref/models/querysets/…

Mouscellaneous · Accepted Answer · 2015-09-18 13:56:22Z

61

If you just want to count the distinct values, you can use the distinct() and count() functions:

count = Project.objects.values('informationunit__username').distinct().count()

answered Sep 18, 2015 at 13:56

Mouscellaneous

2,7143 gold badges28 silver badges37 bronze badges

1 Comment

allardbrain Over a year ago

Thank you, this is what I was looking for. The accepted answer returns a list of objects. Your solution worked for me.

Rag Sagar · Accepted Answer · 2012-10-31 05:21:18Z

16

Project.objects.all().annotate(Count('informationunit__username', 
                                     distinct=True))

edited Oct 31, 2012 at 5:21

answered Oct 30, 2012 at 18:24

Rag Sagar

2,4241 gold badge21 silver badges22 bronze badges

Comments

WeizhongTu · Accepted Answer · 2019-03-18 07:08:15Z

10

SQL SELECT field1, COUNT(DISTINCT(pk)) FROM project GROUP BY field1 ORDER BY NULL;

QuerySet

Project.objects.all().values(field1).annotate(count=Count('pk', distinct=True)).order_by()

answered Mar 18, 2019 at 7:08

WeizhongTu

6,4344 gold badges39 silver badges52 bronze badges

1 Comment

Paul Bissex Sep 26 at 17:44

The field name in values needs to be quoted.

Collectives™ on Stack Overflow

Django annotate count with a distinct field

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related