id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
23669	ProgrammingError when aggregating over an annotated & grouped ORM query	Gregory Goltsov	nobody	"I have a simplified Django model:

{{{
#!python
class Module(models.Model):
    score = models.IntegerField()
    user = models.ForeignKey(User)
}}}

I was trying to construct a query to get the average highest score per module per user. In SQL, the query would look something like:

{{{
#!sql
SELECT
    m.id
    AVG(max_score)
FROM
    (
        SELECT
            m.id
            Max(m.score) AS max_score
        FROM
            module m
        GROUP BY
            m.user_id,
            m.id
    ) f
GROUP BY
    m.id
}}}

In Django ORM the query looks like:

{{{
#!python
Module.objects.values(     # GROUP BY module_id, user_id
    'id', 'user'
).annotate(                # adding MAX(score) to the SELECT clause
    max_score=Max('score')
).aggregate(               # actually getting the average
    Avg('max_score')
)
}}}

This case is even documented in the docs: https://docs.djangoproject.com/en/1.6/topics/db/aggregation/#aggregating-annotations

However, upon evaluating, I encounter the following ProgrammingError:

{{{
ProgrammingError: syntax error at or near ""FROM""
LINE 1: SELECT  FROM (SELECT
}}}

The ORM query looks like a perfectly normal query, no hacking, so I was really surprised to see this behaviour. Furthermore, I'm not the only one having this issue, dating as far as 2010:
 * https://stackoverflow.com/questions/2558992/programmingerror-when-aggregating-over-an-annotated-grouped-django-orm-query
 * https://stackoverflow.com/questions/23279393/count-and-max-after-values-method-on-django-query
"	Bug	closed	Database layer (models, ORM)	1.6	Normal	fixed	orm, annotate, aggregate, values, avg, max		Unreviewed	0	0	0	0	0	0