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" <[email protected]> wrote:
>> Queb wrote:
>>
>> > On Jul 2, 10:39 am, "Michael Bayer" <[email protected]> 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 [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
-~----------~----~----~----~------~----~------~--~---
