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.
$ 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')) LIMIT 21; args=()
[]
>>> 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'))
>>>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/a69957e1-8238-4aaa-bc9e-61e512335d16%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
