How to do SQL inner queries through SQLAlchemy?

Advanced SQL query with sub queries, group by, count and sum functions in to SQLalchemy

• I have written the following query. select distinct(table3.*), (select count(*) from table2 where table2.cus_id = table3.id) as count, (select sum(amount) from table2 where table2.cus_id = table3.id) as total from table2, table1, table3 where table3.id = table2.cus_id and table2.own_id = table1.own_id; It finds the sum of a column and the number of rows that produce the sum as well as some associated data from another table. (Feel free to optimise if you think it can be improved) I need to convert this in to SQLAlchemy but have no idea where to start. I'd appreciate any advice.

• Answer:

  Here's my re-write of your query: SELECT t3.*, x.count, x.amount FROM TABLE3 t3 JOIN (SELECT t2.cus_id COUNT(*) AS count, SUM(t2.amount) AS total FROM TABLE2 t2 WHERE EXISTS(SELECT NULL FROM TABLE1 t1 WHERE t1.own_id = t2.own_id) GROUP BY t2.cus_id) x ON x.cus_id = t3.id Can't help you with the SQLAlchemy part, sorry.