title: Usage of a COUNT(DISTINCT field) with a GROUP BY clause in Django
body: <h2>Problem</h2>
  
  <p>I want to use a <code>COUNT(DISTINCT field)</code> with a <code>GROUP BY</code> clause in Django. As I understand, the <code>COUNT(DISTINCT...</code> can only be achieved by using an <code>extra</code> for the query set.</p>
  
  <p>My simplified model is :</p>
  
  <pre class="lang-py prettyprint-override"><code>class Site(models.Model):
      name = models.CharField(max_length=128, unique=True)
  
  class Application(models.Model):
      name = models.CharField(max_length=64)
      version = models.CharField(max_length=13, db_index=True)
  
  class User(models.Model):
      name = models.CharField(max_length=64) 
      site = models.ForeignKey(Site, db_index=True) 
  
  class Device(models.Model):
      imei = models.CharField(max_length=16, unique=True)
  
      applications = models.ManyToManyField(Application, null=True, db_index=True, through='ApplicationUsage') 
      user = models.ForeignKey(User, null=True, db_index=True)
  
  class ApplicationUsage(models.Model):
      activity = models.DateField(db_index=True)
  
      application = models.ForeignKey(Application)
      device = models.ForeignKey(Device)
  </code></pre>
  
  <p>My goal is to have a liste of Site objects with a count of distinct device for each site given an application activity through a time period, something like </p>
  
  <pre><code>stats_site.name     deviceCount
  ALBI                32
  AMPLEPUIS           42
  ...
  </code></pre>
  
  <p>I try this code :</p>
  
  <pre class="lang-py prettyprint-override"><code>qs = models.Site.objects.filter(user__device__applicationusage__activity__range=[startDay, endDay])\
                              .extra(select={'deviceCount' : 'COUNT(DISTINCT `stats_device`.`id`)'})\
                              .values('name', 'deviceCount')\
  </code></pre>
  
  <p>The generated SQL is :</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT (COUNT(DISTINCT stats_device.id)) AS deviceCount, stats_site.name
  FROM stats_site
  INNER JOIN stats_user ON (stats_site.id = stats_user.site_id)
  INNER JOIN stats_device ON (stats_user.id = stats_device.user_id)
  INNER JOIN stats_applicationusage ON (stats_device.id = stats_applicationusage.device_id)
  WHERE stats_applicationusage.activity BETWEEN '2013-07-01' AND '2013-07-03'
  </code></pre>
  
  <p>And the result is obviously wrong since it lacks the <code>GROUP BY</code> clause, which should be <code>GROUP BY stats_site.name</code></p>
  
  <p>The problem is: I don't know how to add the correct <code>GROUP BY</code> using the <code>annotate</code> function or other.</p>
  
  <h2>Solution</h2>
  
  <p>Using <code>distinct=True</code> on the <code>Count</code> function with <code>annotate</code>:</p>
  
  <pre><code>qs = models.Site.objects.filter(habileouser__device__applicationusage__activity__range=[startDay, endDay])\
                              .annotate(deviceCount=Count('habileouser__device', distinct=True))\
                              .values('name', 'deviceCount')
  </code></pre>