On Jul 2, 2:00 pm, "Michael Bayer" <[email protected]> wrote:
> 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.
Ahh this was exactly what I needed to see. Basically just craft a
select using the [union.c.column,...] , this let me apply the sum()
function to the union of the count(*) and literal columns and as long
as I give the columns matching labels in the selects the group by
works as intended.
Exactly what I needed. Trying to do .select off the union was the
wrong approach, as it was using the defined columns property at that
point. Building my own select and explictly setting the columns from
the union worked like a charm and will be noted for the future.
Thanks and sorry for the triple post early my G1 was acting up.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
