Re: sql help - group by with subselect

Ed,
If you put the subquery in the group by it will work. You can't have alias references in group by

    Try that with any function call using a column alias and the same error will occur.

  select col_1,to_char(sysdate) today,count(*) row_count from your_table   group by col_1,today
  if you change group by to col_1,to_char(sysdate)    it will work.

Your case would required the "selects" subqueries in the group by.

hth
Dave Fowler

"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
>
>
>
>
Received on Tue Apr 24 2001 - 15:46:10 CDT