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
21524	Annotation and .extra with group function breaks at database level	eu@…	nobody	"This is somewhat related to #14657 and #13274

For some awkward reason I have the following queryset:

{{{#!python
qs = MyModel.objects.all()
qs = qs.annotate(
    total=Sum('statstable__total'), 
    closed=Sum('statstable__closed'),
    opened=Sum('statstable__opened'),
    #opened_percent=Avg('statstable__opened_percent'), # The average of the percents is not the correct ratio
    #closed_percent=Avg('statstable__closed_percent'), # value between closed and total
).filter(total__gte=min_total)
qs = qs.extra(
    select={
        'closed_percent': 'SUM(statstable.closed)/SUM(statstable.total)*100',
        'opened_percent': 'SUM(statstable.opend)/SUM(statstable.total)*100',
    }
)
}}}

When you try to do this on Oracle, it will put 'closed_percent' and 'opened_percent' on GROUP BY list, which will raise a `DatabaseError: ORA-00934: group function is not allowed here`.

Since we are not supposed to mess with "".group_by"" without Django's inner code, there is no way to solve this problem.

The quick and dirty solution for me right now was to edit `django/db/models/sql/compiler.py` at line 572 and add the following code:

{{{#!python
                if isinstance(col, (list, tuple)):
                    result.append('%s.%s' % (qn(col[0]), qn(col[1])))
                elif hasattr(col, 'as_sql'):
                    result.append(col.as_sql(qn, self.connection))
                else:
                    if 'avg(' in col.lower() or 'sum(' in col.lower(): 
                        # This is what I added, just checking if the extra select/column has the  
                        # group functions that I need, if it does, skip it, since you 
                        # can't GROUP BY group functions
                        continue
                    result.append('(%s)' % str(col))
}}}

I'm using Django 1.4 on this project (with no ETA to upgrade, unfortunally), and I see that on HEAD it has changed a bit, but remains ""Unconditionally adding the extra_select items"", which is the root of my problems: https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L575"	Uncategorized	closed	Database layer (models, ORM)	1.4	Normal	wontfix	oracle		Unreviewed	0	0	0	0	0	0