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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
