OK your example doesn't really provide enough information as to what the problem is, the exact SQL you want to issue can be generated using the a format as follows, perhaps you can derive the information you need from it:
from sqlalchemy import * from sqlalchemy.sql import table, column invgroups = table('invgroups', column('groupname'), column('groupid'), column('categoryid') ) aa = table('aa', column('type_id')) invtypes = table('invtypes',column('typeid'), column('groupid')) s1 = select([ func.count('*').label('cnt'), invgroups.c.groupname.label('name'), invgroups.c.groupid.label('id') ]).select_from( invgroups.join(invtypes, invgroups.c.groupid==invtypes.c.groupid). join(aa, aa.c.type_id==invtypes.c.typeid) ).group_by( invgroups.c.groupname, invgroups.c.groupid ) s2 = select([ literal("0").label('cnt'), invgroups.c.groupname.label('name'), invgroups.c.groupid.label('id') ]).where(invgroups.c.categoryid==6).group_by(invgroups.c.groupname, invgroups.c.groupid) TT = s2.alias('TT') u = union_all(s1, TT) s = select([ u.c.id, u.c.name, func.sum(u.c.cnt).label('total') ]).group_by(TT.c.name, TT.c.id).order_by(TT.c.name) print s Wayne Witzel wrote: > > Count needs to appear in the group by our be used in an aggregate > function, if I explictly add the literal 'count_1' as it is aliases to > the group by, the SQL executes, but then the count column is not sum'd > with the literal column and produces unwanted results. > > What I need to do is sum the result of the union of the count and > literal columns in the outer select. > > Wayne > > On Jul 2, 11:39 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> Queb wrote: >> >> > On Jul 2, 10:39 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> >> >> whats count() here, do you mean func.count('*') ? >> >> > Sorry, yes, count('*') >> >> the error you're getting regards "count" needing to appear in the group >> by, which suggests the "count" token you're issuing is incorrect. >> >> > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---