title: Aggregate annotated field from Subquery in Django
body: <p>I'm trying to implement a subquery with Django ORM, but I can't find a working solution.
  The SQL query that I would need to reverse-engineer is:</p>
  <pre><code>select t1.location, sum(t1.value_relative::numeric) as total
  from (
      select
          administrative_division_id as &quot;location&quot;,
          jsonb_array_elements(&quot;values&quot;-&gt;'loss'-&gt;'values')-&gt;&gt;'dim_value' as dim_value,
          jsonb_array_elements(&quot;values&quot;-&gt;'loss'-&gt;'values')-&gt;&gt;'value_relative' as value_relative
      from assessments_entrybaserisk
      where &quot;values&quot;-&gt;'scenario'-&gt;&gt;'value' = 'Total'
  ) t1
  where t1.dim_value::numeric = 2
  group by t1.location
  </code></pre>
  <p>I tried to use Django Subquery like below:</p>
  <pre><code>subq = EntryBaseRisk.objects \
      .filter(id=OuterRef('pk'), values__scenario__value='Total') \
      .annotate(
      _dim_value=Cast(
          KeyTextTransform('dim_value',
                           JsonbArrayElements(KeyTransform('values', KeyTransform('loss', 'values')))),
          IntegerField()),
      _value_relative=Cast(
          KeyTextTransform('value_relative',
                           JsonbArrayElements(KeyTransform('values', KeyTransform('loss', 'values')))),
          FloatField())) \
      .values('assessment', 'administrative_division', '_dim_value', '_value_relative')
  
  data = EntryBaseRisk.objects \
      .annotate(
      dim_value=Subquery(subq.values('_dim_value'), output_field=IntegerField()),
      total_relative_value=Sum(Subquery(subq.values('_value_relative'),
                                        output_field=FloatField()))) \
      .filter(**filters) \
      .values('administrative_division', 'total_relative_value') \
      .order_by('administrative_division')
  </code></pre>
  <p>but I get an error <code>ProgrammingError: more than one row returned by a subquery used as an expression</code>.
  The problem is that SQL generated by Django is different from my SQL above, because it doesn't select from a select and it works only if the subquery is returning only one row.
  I can't perform the Sum aggregation in the subquery, because aggregate function calls cannot contain set-returning function calls.
  Is there another way to make Django generate an SQL as per my needs?</p>