programming error in group by clause

[satya]

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'))
>>>

[Collin Anderson]

Hi,

What database are you using?

If you can reproduce this in a simple project, feel free to open a ticket about it.

Thanks,

Collin