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

All Django Annotate Solutions on Gang of Coders

Total of 1 Django Annotate Solutions

Django 1.11 Annotating a Subquery Aggregate