title: Using Subquery to annotate a Count
body: <p>Please help me I've been stuck on this for way too long :( </p>
  
  <h1>What I want to do:</h1>
  
  <p>I have these two models:</p>
  
  <pre><code>class Specialization(models.Model):
      name = models.CharField("name", max_length=64)
  class Doctor(models.Model):
      name = models.CharField("name", max_length=128)
      # ...
      specialization = models.ForeignKey(Specialization)
  </code></pre>
  
  <p>I would like to annotate all specializations in a queryset with the number of doctors that have this specialization.</p>
  
  <h1>My Solution so Far:</h1>
  
  <p>I went through a loop and I made a simple:  <code>Doctor.objects.filter(specialization=spec).count()</code> however this proved to be too slow and inefficient. 
  The more I've read the more I realized that it would make sense to use a <code>SubQuery</code> here to filter the doctors for the <code>OuterRef</code> specialization. This is what I came up with:</p>
  
  <pre><code>doctors = Doctor.objects.all().filter(specialization=OuterRef("id")) \
      .values("specialization_id") \
      .order_by()
  add_doctors_count = doctors.annotate(cnt=Count("specialization_id")).values("cnt")[:1]
  
  spec_qs_with_counts = Specialization.objects.all().annotate(
      num_applicable_doctors=Subquery(add_doctors_count, output_field=IntegerField())
  )
  </code></pre>
  
  <p>The output I get is just 1 for every speciality. The code just annotates every doctor object with its <code>specialization_id</code> and then annotates the count within that group, meaning it will be 1.</p>
  
  <p>This doesn't make complete sense to me unfortunately. In my initial attempt I used an aggregate for the count, and while it works on its own it doesn't work as a <code>SubQuery</code>, I get this error:</p>
  
  <p><code>This queryset contains a reference to an outer query and may only be used in a subquery.</code></p>
  
  <p>I posted this question before and someone suggested doing <code>Specialization.objects.annotate(count=Count("doctor"))</code></p>
  
  <p>However this doesn't work because I need to count a specific queryset of Doctors.</p>
  
  <h1>I've followed these links</h1>
  
  <p>However, I'm not getting the same result:</p>
  
  <ul>
  <li><p><a href="https://docs.djangoproject.com/en/1.11/ref/models/expressions/" rel="noreferrer">https://docs.djangoproject.com/en/1.11/ref/models/expressions/</a></p></li>
  <li><p><a href="https://medium.com/@hansonkd/the-dramatic-benefits-of-django-subqueries-and-annotations-4195e0dafb16" rel="noreferrer">https://medium.com/@hansonkd/the-dramatic-benefits-of-django-subqueries-and-annotations-4195e0dafb16</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/42543978/django-1-11-annotating-a-subquery-aggregate">Django 1.11 Annotating a Subquery Aggregate</a></p></li>
  </ul>
  
  <p>If you have any questions that would make this clearer please tell me.</p>