title: Aggregation of an annotation in GROUP BY in Django
body: <p><strong>UPDATE</strong></p>
  
  <p>Thanks to the posted answer, I found a much simpler way to formulate the problem. The original question can be seen in the revision history.</p>
  
  <h2>The problem</h2>
  
  <p>I am trying to translate an SQL query into Django, but am getting an error that I don't understand. </p>
  
  <p>Here is the Django model I have:</p>
  
  <pre><code>class Title(models.Model):
    title_id = models.CharField(primary_key=True, max_length=12)
    title = models.CharField(max_length=80)
    publisher = models.CharField(max_length=100)
    price = models.DecimalField(decimal_places=2, blank=True, null=True)
  </code></pre>
  
  <p>I have the following data:</p>
  
  <pre><code>publisher                    title_id      price  title
  ---------------------------  ----------  -------  -----------------------------------
  New Age Books                PS2106         7     Life Without Fear
  New Age Books                PS2091        10.95  Is Anger the Enemy?
  New Age Books                BU2075         2.99  You Can Combat    Computer Stress!
  New Age Books                TC7777        14.99  Sushi, Anyone?
  Binnet &amp; Hardley             MC3021         2.99  The Gourmet Microwave
  Binnet &amp; Hardley             MC2222        19.99  Silicon Valley   Gastronomic Treats
  Algodata Infosystems         PC1035        22.95  But Is It User Friendly?
  Algodata Infosystems         BU1032        19.99  The Busy Executive's   Database Guide
  Algodata Infosystems         PC8888        20     Secrets of Silicon Valley
  </code></pre>
  
  <p>Here is what I want to do: introduce an annotated field <code>dbl_price</code> which is twice the price, then group the resulting queryset by <code>publisher</code>, and for each publisher, compute the total of all <code>dbl_price</code> values for all titles published by that publisher.</p>
  
  <p>The SQL query that does this is as follows:</p>
  
  <pre><code>SELECT SUM(dbl_price) AS total_dbl_price, publisher
  FROM (
    SELECT price * 2 AS dbl_price, publisher
    FROM title
  ) AS A 
  GROUP BY publisher
  </code></pre>
  
  <p>The desired output would be:</p>
  
  <pre><code>publisher                    tot_dbl_prices
  ---------------------------  --------------
  Algodata Infosystems                 125.88
  Binnet &amp; Hardley                      45.96
  New Age Books                         71.86 
  </code></pre>
  
  <h2>Django query</h2>
  
  <p>The query would look like:</p>
  
  <pre><code>Title.objects
   .annotate(dbl_price=2*F('price'))
   .values('publisher')
   .annotate(tot_dbl_prices=Sum('dbl_price'))
  </code></pre>
  
  <p>but gives an error: </p>
  
  <pre><code>KeyError: 'dbl_price'. 
  </code></pre>
  
  <p>which indicates that it can't find the field <code>dbl_price</code> in the queryset.</p>
  
  <h2>The reason for the error</h2>
  
  <p>Here is why this error happens: <a href="https://docs.djangoproject.com/en/1.10/topics/db/aggregation/#order-of-annotate-and-values-clauses" rel="noreferrer">the documentation says</a></p>
  
  <blockquote>
    <p>You should also note that average_rating has been explicitly included
         in the list of values to be returned. This is required because of the    ordering of the values() and annotate() clause.</p>
    
    <p>If the values() clause precedes the annotate() clause, any annotations 
         will be automatically added to the result set. However, if the 
         values() clause is applied after the annotate() clause, you need to explicitly include the aggregate column.</p>
  </blockquote>
  
  <p>So, the <code>dbl_price</code> could not be found in aggregation, because it was created by a prior <code>annotate</code>, but wasn't included in <code>values()</code>. </p>
  
  <p>However, I can't include it in <code>values</code> either, because I want to use <code>values</code> (followed by another <code>annotate</code>) as a grouping device, since</p>
  
  <blockquote>
    <p>If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.</p>
  </blockquote>
  
  <p>which is the basis of how Django <a href="https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django">implements SQL <code>GROUP BY</code></a>. This means that I can't include <code>dbl_price</code> inside <code>values()</code>, because then the grouping will be based on unique combinations of both fields <code>publisher</code> and <code>dbl_price</code>, whereas I need to group by <code>publisher</code> only.</p>
  
  <p>So, the following query, which only differs from the above in that I aggregate over model's <code>price</code> field rather than annotated <code>dbl_price</code> field, actually works:</p>
  
  <pre><code>Title.objects
   .annotate(dbl_price=2*F('price'))
   .values('publisher')
   .annotate(sum_of_prices=Count('price'))
  </code></pre>
  
  <p>because the <code>price</code> field is in the model rather than being an annotated field, and so we don't need to include it in <code>values</code> to keep it in the queryset.</p>
  
  <h2>The question</h2>
  
  <p>So, here we have it: I need to include annotated property into <code>values</code> to keep it in the queryset, but I can't do that because <code>values</code> is also used for grouping (which will be wrong with an extra field). The problem essentially is due to the two very different ways that <code>values</code> is used in Django, depending on the context (whether or not <code>values</code> is followed by <code>annotate</code>) - which is (1) value extraction (SQL plain <code>SELECT</code> list) and (2) grouping + aggregation over the groups (SQL <code>GROUP BY</code>) - and in this case these two ways seem to conflict.</p>
  
  <p><strong>My question is</strong>: is there any way to solve this problem (without things like falling back to raw sql)?</p>
  
  <p><em>Please note:</em> the specific example in question can be solved by moving all <code>annotate</code> statements after <code>values</code>, which was noted by several answers. However, I am more interested in solutions (or discussion) which would keep the <code>annotate</code> statement(s) before <code>values()</code>, for three reasons: 1. There are also more complex examples, where the suggested workaround would not work. 2. I can imagine situations, where the annotated queryset has been passed to another function, which actually does GROUP BY, so that the only thing we know is the set of names of annotated fields, and their types. 3. The situation seems to be pretty straightforward, and it would surprise me if this clash of two distinct uses of <code>values()</code> has not been noticed and discussed before. </p>