CODEWITHSUNDEEP
group-bysqlalchemy
bu_
bu_

Reputation: 11

sqlalchemy group_by error

The following works

s = select([tsr.c.kod]).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.

Upvotes: 1

Views: 1129

Answers (2)

Wolph
Wolph

Reputation: 80061

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.

Upvotes: 1

vonPetrushev
vonPetrushev

Reputation: 5599

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.

Upvotes: 0

Related Questions