I have a pair of selects that I am using with union_all and I'd like to have better control of aliasing so I can use sum on the column in the outer select that is made up of the count() and literal columns from the unioned selects.
s1 = select([count(), id, name], cat_id == 6, [join(item_tbl, aa_tbl, Item.id == AA.item_id).join(grp_tbl, Group.id == Item.group_id)], group_by=[Group.name, Group.id]) s2 = select([literal(0), id, name], cat_id == 6, group_by=[Group.name, Group.id] u1 = union_all(s1, s2).alias('group_with_zero').select(group_by= ['name','id']) When I execute u1 I get the expect error that count _1 needs to be part of the group by or used in an aggregate. Ideally I'd like the outer select to be performing a sum() on that column. But I'm drawing a blank in figuring out how to control the aliasing explicitly so I can make that happen. Am I going about this backwards or missing the obvious? Thanks, Wayne Here is the SQL I hand crafted before I started building the expr repr. select id, name, sum(cnt) as total from ( (select count(*) as cnt, groupname as name, invgroups.groupid as id from invgroups join invtypes on invtypes.groupid = invgroups.groupid join aa on aa.type_id = invtypes.typeid group by groupname, invgroups.groupid) UNION ALL (select 0 as cnt, groupname as name, groupid as id from invgroups where categoryid = 6 group by groupname, groupid) ) as TT group by TT.name, TT.id order by TT.name --~--~---------~--~----~------------~-------~--~----~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---