stock_request = select( [stock_request_join] + \ [stock_table.c.quantity.op('-') (func.sum(request_table.c.quantity)).label('unordered')] + \ [stock_table.c.quantity.op('-') (func.sum(request_table.c.allocation)).label('unallocated')], group_by=[c for c in stock_request_join.c], correlate=False).alias('stock_request')

This generates the right SQL. But I can't get it to work with the original column names, then I get name ambigious errors.

When I do it with the joined names, the mapper tries to insert a request too when I want to create stock which makes kind of sense.

When I remove [stock_request_join] and insert [c for c in stock_request_join.c] the JOIN syntax is being removed and it does a regular select against request and stock.

Basically I want to end up with a regular Stock object with the aggregrate columns request_table.c.quantity and request_table.c.allocation. I have it working with a regular select, but if there is no request in te table linked to a stock it does not return that stock when doing a session.query(Stock).select(). Left outer joining request would do the trick.