On Aug 23, 2:11 am, OKB <[EMAIL PROTECTED]> wrote: > ... let's say I have a table with columns Firstname, Lastname, > and Income. What I'd like is to get query results where the columns > are Firstname, Lastname, Income, "sum of incomes of everyone with this > Firstname", "sum of incomes of everyone with this Lastname". > > Does SQLAlchemy have the smarts to allow me to somehow specify "group > by" information on a per-column basis, and then under the hood > generate the joins necessary to retrieve the aggregates I want? If > so, how can I do this?
Yes, you can do the joins in SQLAlchemy. Here is one way using column_property. I'm sure you can make it simpler than this, and you can also 'automate' if you want to make it simpler for complex cases. from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine("sqlite://", echo=True) metadata = MetaData(bind=engine) sm = sessionmaker(autoflush=True, bind=engine) session = scoped_session(sm) mapper = session.mapper tabl = Table('testa', metadata, Column('first',String(12)), Column('last',String(12)), Column('pay',Integer), PrimaryKeyConstraint('first','last',name='fullname')) class Tabl(object): pass tabfirst = tabl.alias('tabfirst') selfirst = select([func.sum(tabfirst.c.pay)], tabl.c.first==tabfirst.c.first).as_scalar().label('sumfirst') tablast = tabl.alias('tablast') sellast = select([func.sum(tablast.c.pay)], tabl.c.last==tablast.c.last).as_scalar().label('sumlast') mapper(Tabl, tabl, properties=dict( sumfirst=column_property(selfirst), sumlast=column_property(sellast) )) tabl.create() u1 = Tabl(first='George',last='Washington',pay=100) u2 = Tabl(first='George',last='Hamilton',pay=200) u3 = Tabl(first='Martha',last='Washington',pay=80) session.commit() q = session.query( Tabl ) for obj in q: print "%-12s%-12s%4d All %-12s=%4d All %-12s=%4d" % \ (obj.first, obj.last, obj.pay, \ obj.first,obj.sumfirst, obj.last,obj.sumlast) Output: George Washington 100 All George = 300 All Washington = 180 George Hamilton 200 All George = 300 All Hamilton = 200 Martha Washington 80 All Martha = 80 All Washington = 180 The query: SELECT (SELECT sum(tabfirst.pay) AS sum_1 FROM testa AS tabfirst WHERE testa.first = tabfirst.first) AS sumfirst, (SELECT sum(tablast.pay) AS sum_2 FROM testa AS tablast WHERE testa.last = tablast.last) AS sumlast, testa.first AS testa_first, testa.last AS testa_last, testa.pay AS testa_pay FROM testa -- Kip Bryan --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---