title: Django 1.11 Annotating a Subquery Aggregate
body: <p>This is a bleeding-edge feature that I'm currently skewered upon and quickly bleeding out. I want to annotate a subquery-aggregate onto an existing queryset. Doing this before 1.11 either meant custom SQL or hammering the database. <a href="https://docs.djangoproject.com/en/1.11/ref/models/expressions/#using-aggregates-within-a-subquery-expression" rel="noreferrer">Here's the documentation for this</a>, and the example from it:</p>
  
  <pre><code>from django.db.models import OuterRef, Subquery, Sum
  comments = Comment.objects.filter(post=OuterRef('pk')).values('post')
  total_comments = comments.annotate(total=Sum('length')).values('total')
  Post.objects.filter(length__gt=Subquery(total_comments))
  </code></pre>
  
  <p>They're <em>annotating</em> on the aggregate, which seems weird to me, but whatever.</p>
  
  <p>I'm struggling with this so I'm boiling it right back to the simplest real-world example I have data for. I have <code>Carpark</code>s which contain many <code>Space</code>s. Use <code>Book→Author</code> if that makes you happier but —for now— I just want to annotate on a count of the related model using <code>Subquery</code>*.</p>
  
  <pre><code>spaces = Space.objects.filter(carpark=OuterRef('pk')).values('carpark')
  count_spaces = spaces.annotate(c=Count('*')).values('c')
  Carpark.objects.annotate(space_count=Subquery(count_spaces))
  </code></pre>
  
  <p>This gives me a lovely <code>ProgrammingError: more than one row returned by a subquery used as an expression</code> and in my head, this error makes perfect sense. The subquery is returning a list of spaces with the annotated-on total.</p>
  
  <p>The example suggested that some sort of magic would happen and I'd end up with a number I could use. But that's not happening here? How do I annotate on aggregate Subquery data?</p>
  
  <h2>Hmm, something's being added to my query's SQL...</h2>
  
  <p>I built a new Carpark/Space model and it worked. So the next step is working out what's poisoning my SQL. On Laurent's advice, I took a look at the SQL and tried to make it more like the version they posted in their answer. And this is where I found the real problem:</p>
  
  <pre><code>SELECT "bookings_carpark".*, (SELECT COUNT(U0."id") AS "c"
  FROM "bookings_space" U0
  WHERE U0."carpark_id" = ("bookings_carpark"."id")
  GROUP BY U0."carpark_id"<strong>, U0."space"</strong>
  )
  AS "space_count" FROM "bookings_carpark";</code></pre>
  
  <p>I've highlighted it but it's that subquery's <code>GROUP BY ... U0."space"</code>. It's retuning both for some reason. Investigations continue.</p>
  
  <p>Edit 2: Okay, just looking at the subquery SQL I can see that second group by coming through ☹</p>
  
  <pre><code>In [12]: print(Space.objects_standard.filter().values('carpark').annotate(c=Count('*')).values('c').query)
  SELECT COUNT(*) AS "c" FROM "bookings_space" GROUP BY "bookings_space"."carpark_id", "bookings_space"."space" ORDER BY "bookings_space"."carpark_id" ASC, "bookings_space"."space" ASC
  </code></pre>
  
  <p><strong>Edit 3</strong>: Okay! Both these models have sort orders. These are being carried through to the subquery. It's these orders that are bloating out my query and breaking it.</p>
  
  <p>I guess this might be a bug in Django but short of removing the Meta-order_by on both these models, <strong>is there any way I can <em>unsort</em> a query at querytime?</strong></p>
  
  <hr>
  
  <p><sub>*<strong>I know I could just annotate a Count for this example</strong>. My real purpose for using this is a much more complex filter-count but I can't even get this working.</sub></p>