sqlalchemy group_by error

The following works

s = select([tsr.c.kod])开发者_Python百科.where(tsr.c.rr=='10').group_by(tsr.c.kod)

and this does not:

s = select([tsr.c.kod, tsr.c.rr, any fields]).where(tsr.c.rr=='10').group_by(tsr.c.kod) 

Why?

thx.

It doesn't work because the query isn't valid like that.

Every column needs to be in the group_by or needs an aggregate (i.e. max(), min(), whatever) according to the SQL standard. Most databases have always complied to this but there are a few exceptions.

MySQL has always been the odd one in this regard, within MySQL this behaviour depends on the ONLY_FULL_GROUP_BY setting: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

I would personally recommend setting the sql_mode setting to ANSI. That way you're largely compliant to the SQL standard which will help you in the future if you ever need to use (or migrate) to a standards compliant database such as PostgreSQL.

What you are trying to do is somehow valid in mysql, but invalid in standard sql, postgresql and common sense. When you group rows by 'kod', each row in a group has the same 'kod' value, but different values for 'rr' for example. With aggregate functions you can get some aspect of the values in this column for each group, for example

select kod, max(rr) from table group by kod

will give you list of 'kod's and the max of 'rr's in each group (by kod).

That being sad, in the select clause you can only put columns from the group by clause and/or aggregate functions from other columns. You can put whatever you like in where - this is used for filtering. You can also put additional 'having' clause after group that contains aggregate function expression that can also be used as post-group filtering.