[Solved]-Django ORM version of SQL COUNT(DISTINCT <column>)

by

9👍

✅

You can indeed use distinct and count together, as seen on this answer: https://stackoverflow.com/a/13145407/237091

In your case:

SELECT sender_id, COUNT(id), COUNT(DISTINCT recipient_id)
FROM myapp_messages
GROUP BY sender_id;

would become:

Message.objects.values('sender').annotate(
    message_count=Count('sender'),
    recipient_count=Count('recipient', distinct=True))
👤Scott Stafford

4👍

from django.db.models import Count

messages = Message.objects.values('sender').annotate(message_count=Count('sender'))

for m in messages:
    m['recipient_count'] = len(Message.objects.filter(sender=m['sender']).\
                              values_list('recipient', flat=True).distinct())
👤jurgenreza

Leave a comment