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 [email protected].
To post to this group, send email to [email protected].
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.
