Re: sql help - group by with subselect

perhaps it has to do with ambiguous column reference in the where clause of the subselects on the LHS of the =.

"ed zappulla" <zappullae_at_rcn.com> wrote in message news:9c4fbe$7c5$1_at_bob.news.rcn.net...
> yea it was early. i cut the code from a file. here it is cleaned up. I
> agree I can use a join but I'd also like to learn why this doesn't work.
> What am I missing? The column state is in the select but can't be used as
> part of the aggregate list.
>
> SQL> select airport_cd,
> 2 (select country.name from country where
> country_id=airport.country_id) as country,
> 3 (select state.name from state where state_id=airport.state_id) as
> state,
> 4 city
> 5 from airport
> 6 group by airport_cd, country, state, city
> 7 order by airport_cd
> 8 ;
> group by airport_cd, country, state, city
> *
> ERROR at line 6:
>
>
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:teb6n43pfv04e4_at_beta-news.demon.nl...
> >
> > "ed zappulla" <zappullae_at_rcn.com> wrote in message
> > news:9c3u9j$sk$1_at_bob.news.rcn.net...
> > > Does anyone know why I can't use the country or state in the group by?
> > >
> > > SQL>
> > > select airport_cd,
> > > (select country.name from country where
 country_id=airport.country_id)
 as
> > > country,
> > > (select state.name from state where state_id=airport.state_id) as
 state,
> > > city
> > > from airport,
> > > group by airport_cd, country, state
> > > order by airport_cd
> > > ;
> > > group by vendor.name, airport_cd, country, state
> > > *
> > > ERROR at line 9:
> > > ORA-00904: invalid column name
> > >
> > >
> > >
> > >
> >
> > Your statement is not correct.
> > First of all your from clause ends with a ,
> > and that would give rise to error messages anyway.
> > Secondly, you try to group by 2 queries, not by individual columns in
 those
> > queries, so that's not going to work either.
> > What I would do is write this as a simple join, I don't see at all why
 you
> > would be using subqueries.
> > Also you'r missing an aggregate function in the select list.
> > It all looks very much looks like your brain was shutdown at the time of
> > writing. I'm sure this isn't your normal way of writing selects.
> >
> > Regards,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> >
> >
> >
>
>
Received on Tue Apr 24 2001 - 13:18:39 CDT