Hi Massimo, thanks for the suggested query but unfortunately I got this error:
Traceback (most recent call last): File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 208, in restricted ccode = compile2(code,layer) File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 193, in compile2 return compile(code.rstrip().replace('\r\n','\n')+'\n', layer, 'exec') File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py" <http://127.0.0.1:8000/admin/default/edit/bikend/models/clubDB.py>, line 29 dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby, groupby=join(s)) SyntaxError: only named arguments may follow *expression If that can help, I've tried without the wildcard but it failed, web2py was blocked and python took 100% of the cpu. Paolo On Wednesday, October 17, 2012 2:35:23 PM UTC+2, Massimo Di Pierro wrote: > > > s=db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day() > > def join(s): return reduce(lambda a,b:a|b,s) > dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby, > groupby=join(s)) > > On Wednesday, 17 October 2012 01:31:55 UTC-5, Paolo wrote: >> >> 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 >>>> >>> --