Re: sql help - group by with subselect

got it! thanks.....ed

"Dave Fowler" <d.fowler_at_smmj.com> wrote in message news:mClF6.1800$G04.232202_at_newsread2.prod.itd.earthlink.net...
> 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 - 16:15:12 CDT