Re: Subqueries and group by

make it this way:

select name,age from (
select partner.name, (select age from person where partner.pid=person.pid) age
from partner
) group by name,age

or this way (which most likely is faster):

select partner.name,person.age
from partner, personen
where partner.pid=person.pid
group by partner.name,person.age

this should work
your select does not work, because it is a different subselect for oracle (in the select and in the group by clause)

Ralf Wegener wrote:

> o.k. in this example I could do this. But in other cases.
> Is it possible to use a subquery within a group cluase.
>
> Thanks,
>
> Ralf Wegener
>
> Niall Litchfield <n-litchfield_at_audit-commission.gov.uk> schrieb in im
> Newsbeitrag: 3b837b73$0$8505$ed9e5944_at_reading.news.pipex.net...
> > Can you not do
> >
> > select partner.name,person.age
> > from partner,person
> > where partner.pid=person.pid
> > group by partner.name,person.age;
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > "Ralf Wegener" <rwegener_at_gmx.net> wrote in message
> > news:9lvsob$hrp$05$1_at_news.t-online.com...
> > > Hi,
> > >
> > > my problem:
> > >
> > > I would like to use a subquery within a group clause.
> > >
> > > Example:
> > >
> > > select partner.name, (select age from person where
> partner.pid=person.pid)
> > > from partner
> > > group by partner.name, (select age from person where
> > partner.pid=person.pid)
> > >
> > > I get the error:
> > >
> > > select partner.name, (select age from person where
> partner.pid=person.pid)
> > > from partner
> > > *
> > > ORA-00979: not a GROUP BY expression
> > >
> > > Any ideas?
> > >
> > >
> > > Best regards
> > >
> > > Ralf Wegener
> > >
> > >
> > >
> > >
> >
> >
Received on Wed Aug 22 2001 - 06:10:18 CDT