SQL Multiple Table Query

Question

Hi can someone help me with this question please. i spent ages trying to figure out what am doing wrong but no luck

Tables

1. Publisher(publisherName,location, noOftitles)
2. Author(name,location)
3. Book(title,isbn,cost,authorName,publisherName)

Question

Write a SQL command to display for each publisher with more than one author, the publisher’s name, the publisher’s location and the average cost of the books that the publisher sells.

Code:

SELECT Book.publishername, location, avg(cost)
FROM   Publisher
,      Book
WHERE  Publisher.publisherName = Book.publisherName
GROUP 
BY     publisherName
HAVING COUNT (DISTINCT authorname) >1

error

ORA-00918: column ambiguously defined

Right Answer:

SELECT Publisher.publisherName, Publisher.location, avg(cost)
FROM Publisher, Book
WHERE Publisher.publisherName = Book.publisherName
GROUP BY Publisher.PublisherName, Publisher.Location
HAVING COUNT (DISTINCT authorname) >1;

Answer 1

You have ambiguous column names in your select. location in the select statement could be from the Publisher table or Author table and publisherName in the GROUP BY could be from Publisher or Book. You need to explicitly specify which tables you want those values from.

SELECT Book.publishername, Publisher.location, avg(cost)
FROM Publisher, Book
WHERE Publisher.publisherName =Book.publisherName
GROUP BY publisher.publisherName
HAVING COUNT (DISTINCT authorname) >1;

EDIT: Regarding your comment, you have a column in your select that is either not in the Group By or not an aggregate function (e.g. SUM, AVG, etc). You need to include location in the GROUP BY:

SELECT Book.publishername, Publisher.location, avg(cost)
FROM Publisher, Book
WHERE Publisher.publisherName =Book.publisherName
GROUP BY publisher.publisherName, Publisher.location
HAVING COUNT (DISTINCT authorname) >1;

Answer 2

Shouldn't it be GROUP BY Book.publisherName?

Answer 3

location is in two tables. You have to define it by using Table.Column:

Publisher.location

Answer 4

Your group by should read GROUP BY Book.publishername

Answer 5

The answer does explain what is going on. Here is your query:

SELECT Book.publishername, location, avg(cost)
FROM Publisher, Book
WHERE Publisher.publisherName =Book.publisherName
GROUP BY publisherName
---------^
HAVING COUNT (DISTINCT authorname) >1;

Oracle doesn't know where the column comes from. It can be from either table. So, prefix all columns with table aliases. Also, use proper join syntax, instead of implicit joins. Here is a better version of your query:

SELECT b.publishername, p.location, avg(b.cost)
FROM Publisher p join
     Book b
     on p.publisherName = b.publisherName
GROUP BY b.publisherName
HAVING COUNT(DISTINCT b.authorname) > 1;

I guess at the tables where the columns came from.