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.
Jeffrey Kemp :
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\":\n\nselect comp_table.\"avg\"\nfrom (select column_1,avg(column_2) as \"avg\"\n from table_1, group by column_1) comp_table\n",
2010-09-17T04:47:22
Jon :
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]",
2010-09-17T04:27:26