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

Question

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.

OMG Ponies · Accepted Answer · 2010-11-01 23:32:13Z

3

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.

answered Nov 1, 2010 at 23:32

community wiki

OMG Ponies

Sign up to request clarification or add additional context in comments.

1 Comment

eski009 Over a year ago

Great - your query is much better! Just need it converted in to SQLAlchemy - any takers? :-)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related