Thank you! That worked, with one minor modification. It looks like MSSQL requires an alias for an anonymous table (even if you're not joining it with anything). As such, I needed to add .alias() to the query.
In the end, it looked like this: q3 = sqlalchemy.select([partial.label('left_string'), different_column]).alias() q4 = sqlalchemy.select([q3.c.left_string, func.sum(q3.c.different_column)]).group_by(q3.c.left_string) sql_engine.execute(q4).fetchall() On Wednesday, 24 February 2016 09:26:59 UTC-6, Mike Bayer wrote: > > > > On 02/24/2016 10:13 AM, Alex Lowe wrote: > > Hi there, > > > > I'm receiving a ProgrammingError with certain types of query to MSSQL > > (they seem to work fine when querying SQLite though). Either my > > Google-fu is weak or there hasn't been a solution posted publicly, since > > the two most useful-looking pages were these two StackOverflow threads > > with no useful responses: > > > http://stackoverflow.com/questions/18307466/group-by-case-in-sql-server-sqlalchemy > > > > http://stackoverflow.com/questions/21742713/need-a-query-in-sqlalchemy-with-group-by-case > > > > > I'm also very new to SQLAlchemy and have mostly picked it up through a > > combination of web searches and following the examples of my coworkers > > (who picked it up by doing web searches for what they needed), so advice > > on how to make my example code better are welcome. > > I've certainly had to work around this problem but the news that the raw > string works is new to me, but I would assume it has something to do > with the removal of bound parameters. ODBC actually has two different > execution APIs internally that interpret the given statement > differently, one is much more picky about being able to infer the type > of bound parameters, so that might be part of what's going on. > > If i recall correctly the workaround is to make a subquery like this: > > SELECT left_1, sum(different_column) FROM > ( > SELECT left(some_string, ?) AS left_1, different_column > FROM [DEV].dbo.[AML_Test] > ) GROUP BY left_1 > > > so, paraphrasing > > stmt = select([func.left(table.c.some_string, 5).label('left'), > table.c.different_column]) > > stmt = select([stmt.c.left, > func.sum(stmt.c.different_column]).group_by(stmt.c.left) > > > > > > > I've got a table that contains a string column and an integer column, > > and I'm trying to group by substrings. In so doing, it brings up an > > error message about aggregate functions in the group by clause. > > Specifically, if I write this code: > > > > test_table = sqlalchemy.Table('AML_Test', dev_schema) > > some_string = sqlalchemy.Column('some_string', > > sqlalchemy.VARCHAR(length=50)) > > different_column = sqlalchemy.Column('different_column', > sqlalchemy.INT()) > > partial = func.left(some_string, 3) > > aggregate = func.sum(different_column) > > qq = > test_table.select().group_by(partial).column(partial).column(aggregate) > > > > and then run qq.execute(), pyodbc gives me the follow error message: > > > > ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] > > [Microsoft][ODBC SQL Server Driver][SQL Server]Column > > 'DEV.dbo.AML_Test.some_string' is invalid in the select list because it > > is not contained in either an aggregate function or the GROUP BY clause. > > (8120) (SQLExecDirectW)") [SQL: 'SELECT left(some_string, ?) AS left_1, > > sum(different_column) AS sum_1 \nFROM [DEV].dbo.[AML_Test] GROUP BY > > left(some_string, ?)'] [parameters: (3, 3)] > > > > > > My workaround for the moment is to cast the compiled statement to a > > string and execute that string, but it's unclear to me why that would do > > anything different (despite the fact that it does). > > > > > > c.session.execute( > > str(qq.selectable.compile(compile_kwargs={'literal_binds': True})) > > ).fetchall() > > > > > > If anyone can explain to me what I'm doing wrong and how to fix it, I'd > > be extremely grateful. > > > > > > Thanks, > > > > > > Alex > > > > -- > > You received this message because you are subscribed to the Google > > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > > an email to sqlalchemy+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.