SQL error:Invalid identifier

[Victor Ramen]

Hello,
I am sql rookie and I would appreciate very much some assistance on this rather basic issue.

select comp_table.* from (select column_1,avg(column_2) as "avg" from table_1, group by column_1) comp_table---> returns correct records with 2 columns named : column_1, avg;

But if I change to :

select comp_table.avg from (select column_1,avg(column_2) as "avg" from table_1, group by column_1) comp_table---> returns Error: Invalid identifier "avg"

The thing is I only need to select the avg column; So I cannot do select comp_table.*. Can you guys please help?
Also, if you could kindly provide some tuning tips for the query...that would be awesome.

Thank you.

[Byron Mabbett]

Hi Roy,
You just need to not define your column as a literal. (No need for the quotes around the column name)
Use

 
select
  comp_table.avg
from
(
  select
    column_1,
    avg(column_2) as avg
  from
    table_1
  group by
    column_1
) comp_table

If you just need the avg column with out the identifier you do not need the inner query

 
  select
    avg(column_2) as avg
  from
    table_1
  group by
    column_1

Also it can be best to try to stay away from what may be reserved words in any language like avg for column heading. Who knows if someone is going to import the results into a spreadsheet or such like.

[Campbell Ritchie]

Welcome to the Ranch , Byron Mabbett

[Victor Ramen]

Thank you. It solved the problem.