Create new table from average of multiple columns in multiple tables

Question

I have the following query:

CREATE TABLE Professor_Average

SELECT Instructor, SUM( + instreffective_avg + howmuchlearned_avg + instrrespect_avg)/5
FROM instreffective_average, howmuchlearned_average, instrrespect_average
GROUP BY Instructor;

It is telling me that Instructor is ambiguous. How do I fix this?

That's your database trying to prevent you from doing something unwise. One of the principles of normalization is to not store calculated values. If you don't understand why, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk, Feb 10 '13 at 01:01

score 2 · Answer 1 · answered Feb 10 '13 at 00:50

Qualify instructor with the name of the table it came from.

For example: instreffective_average.Instructor

If you don't do this, SQL will guess which table of the query it came from, but if there are 2 or more possibilities it doesn't try to guess and tells you it needs help deciding.

score 1 · Answer 2 · edited May 23 '17 at 12:27

Your query most likely fails in more than one way.

In addition to what @Patashu told you about table-qualifying column names, you need to JOIN your tables properly. Since Instructor is ambiguous in your query I am guessing (for lack of information) it could look like this:

SELECT ie.Instructor
      ,SUM(ie.instreffective_avg + h.howmuchlearned_avg + ir.instrrespect_avg)/5
FROM   instreffective_average ie
JOIN   howmuchlearned_average h  USING (Instructor)
JOIN   instrrespect_average   ir USING (Instructor)
GROUP  BY Instructor

I added table aliases to make it easier to read.

This assumes that the three tables each have a column Instructor by which they can be joined. Without JOIN conditions you get a CROSS JOIN, meaning that every row of every table will be combined with every row of every other table. Very expensive nonsense in most cases.

USING (Instructor) is short syntax for ON ie.Instructor = h.Instructor. It also collapses the joined (necessarily identical) columns into one. Therefore, you would get away without table-qualifying Instructor in the SELECT list in my example. Not every RDBMS supports this standard-SQL feature, but you failed to provide more information.

Create new table from average of multiple columns in multiple tables

2 Answers2