CODEWITHSUNDEEP
sqloracle-database
alwaysStuckJava
alwaysStuckJava

Reputation: 369

Creating a table with avg using a join

I'm trying to create a table in which I can acquire an average and then group these averages by name. This is the current SQL I have written and it doesn't seem to be working. The error I am getting is

SQL Error: ORA-00907: missing right parenthesis

Here is the SQL below. Can anyone help? Thanks 

CREATE TABLE new_table_name AS
(Select distinct table_name1.column_name1, AVG(table_name2.column_name2)
FROM  table_name1, table_name2
WHERE table_name1.table_id1 = table_name2.table_id2 
group by table_name1.column_name1
order BY table_name1.column_name1);

Upvotes: 0

Views: 37

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Try this:

create table new_table_name as
select t1.column_name1,
    AVG(t2.column_name2) column_name2
from table_name1 t1
join table_name2 t2 on t1.table_id1 = t2.table_id2
group by t1.column_name1;

Changes made:

  • Use column alias for the AVG(t2.column_name2)
  • DISTINCT not needed as you are doing group by
  • use explicit modern join syntax instead of older comma based join
  • Order by is not required and also, is the real cause of error because a table in RDBMS is a unordered set of rows .
  • Use aliases to cleanup

Upvotes: 2

Related Questions