Q:

aggregate or annotate with condtion in django

I am new to django and SQL queries. I am trying some annotation with distinct in django. but unable to get results. My table looks like
+-----------------------+-----------+---------------------+
| email                 | event     | event_date          |
|-----------------------+-----------+---------------------|
| hector@example.com    | open      | 2017-01-03 13:26:13 |
| hector@example.com    | delivered | 2017-01-03 13:26:28 |
| hector@example.com    | open      | 2017-01-03 13:26:33 |
| hector@example.com    | open      | 2017-01-03 13:26:33 |
| tornedo@example.com   | open      | 2017-01-03 13:34:53 |
| tornedo@example.com   | click     | 2017-01-03 13:35:22 |
| tornedo@example.com   | open      | 2016-09-05 00:00:00 |
| tornedo@example.com   | open      | 2016-09-17 00:00:00 |
| sparrow@example.com   | open      | 2017-01-03 16:05:36 |
| tornedo@example.com   | open      | 2017-01-03 20:12:15 |
| hector@example.com    | open      | 2017-01-03 22:06:47 |
| sparrow@example.com   | click     | 2017-01-09 19:46:26 |
| sparrow@example.com   | open      | 2017-01-09 19:47:59 |
| sparrow@example.com   | open      | 2017-01-09 19:48:28 |
| sparrow@example.com   | delivered | 2017-01-09 19:52:24 |
+-----------------------+-----------+---------------------+

Model is like:
class EmailEvent(models.Model):
    event = models.TextField(blank=True, null=True)
    email = models.TextField(blank=True, null=True)
    event_date = models.DateTimeField(blank=True, null=True)

I want distinct count for different event column values. For example
EmailEvent.objects.filter(event='open').distinct('email').count() results 3
EmailEvent.objects.filter(event='click').distinct('email').count() results 2
EmailEvent.objects.filter(event='delivered').distinct('email').count() results 2
how to get result with aggregate or annotate in one query ? I want results like
{
'open': 3,
'click': 2,
'delivered': 2,
} 

Thanks !

A:

Try using the following query:
from django.db.models import Q, Count, Case, When # probably need Django 1.8+

EmailEvent.objects.values('email').distinct()\
    .annotate(open_count=Case(When(Q(event="open"), then=1)), 
              click_count=Case(When(Q(event="click"), then=1)),
              delivered_count=Case(When(Q(event="delivered"), then=1)))\
    .aggregate(open=Count("open_count"),
               click=Count("click_count"),
               delivered=Count("delivered_count"))

