SQL error: Invalid identifier

Question

I am an SQL rookie and I would very much appreciate 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 and 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.

Answer 1

When the column name is not enclosed in "double quotes", the name is normalized to uppercase; therefore, you were asking for column "AVG", whereas the column name is actually "avg":

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

Answer 2

What database server are you using? AVG is a built-in function in all the ones I know of, so you would need to escape it correctly - which depends on database server. In MS SQL Server it's [avg]