Re: sql help - group by with subselect

From: ed zappulla <zappullae_at_rcn.com>
Date: Tue, 24 Apr 2001 14:06:05 -0400
Message-ID: <9c4fbe$7c5$1@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:06:05 CDT