column must appear in the GROUP BY clause or be used in an aggregate function

[António Ramos]

can i get some help?

it was working with sqlite but not with postgresql

for row in db(db.ficheiros.entity==db.entity.id).select(db.entity.entity,db.entity.id,count,groupby=db.entity.entity):

<class 'psycopg2.ProgrammingError'> column "entity.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT entity.entity, entity.id, COUNT(entity.id) FROM fich... ^

Many thanks

antónio

[villas]

You usually have to include ALL the selected fields in the groupby and orderby clauses.

Anyhow,  the logic of the query doesn't seem correct either.  I think you probably need something like this:

count = db.ficheiros.entity.count()
db(db.ficheiros.entity==db.entity.id).select(db.ficheiros.entity, count,
         groupby=db.ficheiros.entity, orderby=db.ficheiros.entity)

You can always see the SQL generated by printing to the console with _select.  For example:

db(db.ficheiros.entity==db.entity.id)._select(db.ficheiros.entity, count,
         groupby=db.ficheiros.entity, orderby=db.ficheiros.entity)

All the best. 

[Niphlod]

once you group, you can only select the grouped fields and any other field as an aggregate. 

From a set-based logic perspective (which is what "serious" database enforce) it has absolutely no meaning asking for entity.id in your case.