Hi Cliff,
I got the reasons of postgres, but I don't know how to fix it. The query is
actually very simple, I have several post, I want to group them by s, and
get the number of post for each s. Where s is:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
In the select I may created_on and use s instead, something like that:
dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s)
but doing that I got this error:
2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call
last):
File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 209, in
restricted
exec ccode in environment
File
"/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py",
line 140, in <module>
File "/home/paolo/Dropbox/git/web2py/gluon/globals.py", line 184, in
<lambda>
self._caller = lambda f: f()
File
"/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py",
line 3, in index
d= dict(clubs = get_clubs())
File
"/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py", line
25, in get_clubs
dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8787, in select
return adapter.select(self.query,fields,attributes)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 2127, in select
return super(SQLiteAdapter, self).select(query, fields, attributes)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1615, in select
return self._select_aux(sql,fields,attributes)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1596, in
_select_aux
return processor(rows,fields,self._colnames,cacheable=cacheable)
File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1974, in parse
fields[j].type,blob_decode)
IndexError: list index out of range
Paolo
On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote:
>
> I don't know how it possibly worked in sqlite, but this is an aggregate
> query combined with a non-aggregate query.
>
> In other words, the count is a property of an aggregation of rows in the
> database, whereas created_on is a property of individual rows.
>
> This confuses Postgres. It doesn't know if you want the aggregate result
> (count) or the result for individual rows (created_on). It cannot deliver
> both from the same query.
>
> What are you trying to find out in your query?
>
>
>
> On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote:
>>
>> Dear all,
>> I've just switched from sqlite to postgres, and now I have problems with
>> few queries.
>> One query that works correctly on sqlite and fails on postgres is the
>> following:
>> s=db.club.created_on.year() | db.club.created_on.month() |
>> db.club.created_on.day()
>> count = db.club.id.count()
>> dates =
>> db(query).select(db.club.created_on,count,orderby=~db.club.created_on,
>> limitby=limitby, groupby=s)
>>
>> Now on postgres, it raises the following error:
>> ProgrammingError: column "club.created_on" must appear in the GROUP BY
>> clause or be used in an aggregate function
>> LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c...
>>
>> I read online that the fields in the select must be on the groupby as
>> well. The problem is that by grouping even by club.created_on (by adding
>> groupby=s | club.created_on) the result is totally different. What can I
>> do to tackle this problem?
>>
>> Best,
>> Paolo
>>
>
--
