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
On Sunday, January 25, 2015 at 12:51:31 AM UTC-5, satya wrote:
>
> 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/6c04a55c-3f45-401c-b8f4-367d88edc4fc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
