Need some help on fixing the following error. I recently moved
to 1.8alpha version from an older version django-e9d1f11 and I started
seeing this error. Looks like the way group by is constructed has
changed.
Here is the model that I have.
class everydaycost(models.Model):
cost = models.DecimalField(max_digits=20, decimal_places=2)
rev = models.DecimalField(max_digits=20, decimal_places=2)
ts = models.DateField(db_index=True)
I am trying to get roi (rev/cost) for each day of week.
$ python manage.py shell
Python 2.7.5 (default, Mar 9 2014, 22:15:05)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.0.68)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>>
>>> from testgroupby.models import *
>>> from django.db.models import Q, Count, Min, Sum, Func, FloatField
>>> qs = everydaycost.objects.all()
>>> qs = qs.extra(select={'dow': "TO_CHAR(ts, 'D')"})
>>> qs = qs.values('dow')
>>> qs = qs.annotate(sum_cost=Sum('cost'))
>>> qs = qs.annotate(sum_rev=Sum('rev'))
>>> qs = qs.annotate(roi=Func(A='CAST(Sum("rev") as numeric)', B='CAST(Sum("cost") as numeric)',
... template='ROUND(COALESCE(%(A)s / NULLIF(%(B)s,0), 0), 2)', output_field=FloatField()))
>>> print qs
DEBUG (0.002) SELECT (TO_CHAR(ts, 'D')) AS "dow", SUM("testgroupby_everydaycost"."cost") AS "sum_cost", SUM("testgroupby_everydaycost"."rev")
AS "sum_rev", ROUND(COALESCE(CAST(Sum("rev") as numeric) /
NULLIF(CAST(Sum("cost") as numeric),0), 0), 2) AS "roi" FROM
"testgroupby_everydaycost" GROUP BY (TO_CHAR(ts, 'D')),
ROUND(COALESCE(CAST(Sum("rev") as numeric) / NULLIF(CAST(Sum("cost") as
numeric),0), 0), 2) LIMIT 21; args=()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Downloads/Django-1.8a1/django/db/models/query.py", line 139, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/Downloads/Django-1.8a1/django/db/models/query.py", line 163, in __iter__
self._fetch_all()
File "/Downloads/Django-1.8a1/django/db/models/query.py", line 955, in _fetch_all
self._result_cache = list(self.iterator())
File "/Downloads/Django-1.8a1/django/db/models/query.py", line 1075, in iterator
for row in self.query.get_compiler(self.db).results_iter():
File "/Downloads/Django-1.8a1/django/db/models/sql/compiler.py", line 780, in results_iter
results = self.execute_sql(MULTI)
File "/Downloads/Django-1.8a1/django/db/models/sql/compiler.py", line 826, in execute_sql
cursor.execute(sql, params)
File "/Downloads/Django-1.8a1/django/db/backends/utils.py", line 80, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/Downloads/Django-1.8a1/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "/Downloads/Django-1.8a1/django/db/utils.py", line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/Downloads/Django-1.8a1/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
ProgrammingError: aggregate functions are not allowed in GROUP BY
LINE 1: ... GROUP BY (TO_CHAR(ts, 'D')), ROUND(COALESCE(CAST(Sum("rev")...
^
>>> print qs.query
SELECT (TO_CHAR(ts, 'D')) AS "dow", SUM("testgroupby_everydaycost"."cost") AS "sum_cost", SUM("testgroupby_everydaycost"."rev")
AS "sum_rev", ROUND(COALESCE(CAST(Sum("rev") as numeric) /
NULLIF(CAST(Sum("cost") as numeric),0), 0), 2) AS "roi" FROM
"testgroupby_everydaycost" GROUP BY (TO_CHAR(ts, 'D')),
ROUND(COALESCE(CAST(Sum("rev") as numeric) / NULLIF(CAST(Sum("cost") as
numeric),0), 0), 2)
>>>
With earlier version, this worked fine for me.
Type "help", "copyright", "credits" or "license" for more information.