title: Annotating an annotate with Django's ORM
body: <p>I'm trying to find the average score in a given month. </p>
  
  <p>So I want the average score for a given day. An individual could compete more than 1 task a day, so I want the average score for that day. </p>
  
  <p>Then I want the average score for the month. </p>
  
  <p>This is the raw sql I would like django to do:</p>
  
  <pre><code>SELECT AVG(avg_score) as mon_avg, EXTRACT(year FROM c_date) as y, 
  EXTRACT(month FROM c_date) as m
  FROM ( 
     SELECT AVG(SCORE) AS avg_score, DATE(created) as c_date
     FROM companies_task
     GROUP BY c_Date 
  ) as DAY_AVERAGE  GROUP BY y, m
  ORDER BY y, m
  </code></pre>
  
  <p>My model looks like this:</p>
  
  <pre><code>Task(models.Model):
      score = models.IntegerField()
      completed = models.DateTimeFiled(auto_now=True)
  </code></pre>
  
  <p>My current attempt is this:</p>
  
  <pre><code>Task.objects.filter(created=year_ago())\
      .extra(select={"date": "date(created)"})\
      .values('date')\
      .annotate(avg_score=Avg('score'))\
      .extra(select={
          'year': "EXTRACT(year FROM date)",
          'month': "EXTRACT(month from date)"
      })\
      .values('year', 'month')\
      .annotate(month_avg=Avg('avg_score'))
  </code></pre>
  
  <p>The error I get is: </p>
  
  <pre><code>FieldError: Cannot resolve keyword 'year' into field. Choices are: avg_score, created, date, employee, employee_id, id, modified, month, score, year
  
  
  FieldError                                Traceback (most recent call last)
  /usr/local/lib/python3.4/site-packages/django/db/models/sql/query.py in add_fields(self, field_names, allow_m2m)
     1631                 _, targets, _, joins, path = self.setup_joins(
  -&gt; 1632                     name.split(LOOKUP_SEP), opts, alias, allow_many=allow_m2m)
     1633                 targets, final_alias, joins = self.trim_joins(targets, joins, path)
  
  /usr/local/lib/python3.4/site-packages/django/db/models/sql/query.py in setup_joins(self, names, opts, alias, can_reuse, allow_many)
     1404         path, final_field, targets, rest = self.names_to_path(
  -&gt; 1405             names, opts, allow_many, fail_on_missing=True)
     1406 
  
  /usr/local/lib/python3.4/site-packages/django/db/models/sql/query.py in names_to_path(self, names, opts, allow_many, fail_on_missing)
     1329                     raise FieldError("Cannot resolve keyword %r into field. "
  -&gt; 1330                                      "Choices are: %s" % (name, ", ".join(available)))
     1331                 break
  
  FieldError: Cannot resolve keyword 'year' into field. Choices are: created, employee, employee_id, id, modified, score
  
  During handling of the above exception, another exception occurred:
  
  FieldError                                Traceback (most recent call last)
  &lt;ipython-input-12-d207d6f92a38&gt; in &lt;module&gt;()
        3             'month': "EXTRACT(month from date)"
        4         })\
  ----&gt; 5         .values('year', 'month')\
        6         .annotate(avg__somehthing=Avg('avg_score'))
  
  /usr/local/lib/python3.4/site-packages/django/db/models/query.py in values(self, *fields)
      710 
      711     def values(self, *fields):
  --&gt; 712         clone = self._values(*fields)
      713         clone._iterable_class = ValuesIterable
      714         return clone
  
  /usr/local/lib/python3.4/site-packages/django/db/models/query.py in _values(self, *fields)
      705 
      706         query.values_select = field_names
  --&gt; 707         query.add_fields(field_names, True)
      708 
      709         return clone
  
  /usr/local/lib/python3.4/site-packages/django/db/models/sql/query.py in add_fields(self, field_names, allow_m2m)
     1645                                + list(self.annotation_select))
     1646                 raise FieldError("Cannot resolve keyword %r into field. "
  -&gt; 1647                                  "Choices are: %s" % (name, ", ".join(names)))
     1648 
     1649     def add_ordering(self, *ordering):
  
  FieldError: Cannot resolve keyword 'year' into field. Choices are: created, date, employee, employee_id, id, modified, month, score, year
  </code></pre>