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

Publisher(publisherName,location, noOftitles)
Author(name,location)
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;

What RDBMS are you using? Is this homework?

Dan
– Dan

2014-01-22 18:52:41 +00:00
Commented Jan 22, 2014 at 18:52 — Dan
– Dan, Commented Jan 22, 2014 at 18:52
@Dan. Looks like Oracle, from the Error Code.

Shiva
– Shiva

2014-01-22 18:53:45 +00:00
Commented Jan 22, 2014 at 18:53 — Shiva
– Shiva, Commented Jan 22, 2014 at 18:53
@Shiva Well that just plain makes too much sense. :)

Dan
– Dan

2014-01-22 18:54:27 +00:00
Commented Jan 22, 2014 at 18:54 — Dan
– Dan, Commented Jan 22, 2014 at 18:54

Dave Zych · Accepted Answer · 2014-01-22 19:00:50Z

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;

i tried this ^^^ but gives me an error saying: ORA-00979: not a GROUP BY expression
@user2993831 It's due to the location column. Look at my edit.

Arthur · Accepted Answer · 2014-01-22 18:52:52Z

0

Shouldn't it be GROUP BY Book.publisherName?

answered Jan 22, 2014 at 18:52

Arthur

1,5583 gold badges24 silver badges43 bronze badges

Comments

HaleyBuggs · Accepted Answer · 2014-01-22 18:53:55Z

0

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

Publisher.location

answered Jan 22, 2014 at 18:53

HaleyBuggs

9253 gold badges14 silver badges29 bronze badges

Comments

clintperry · Accepted Answer · 2014-01-22 18:53:59Z

0

Your group by should read GROUP BY Book.publishername

answered Jan 22, 2014 at 18:53

clintperry

1921 silver badge8 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2014-01-22 18:54:33Z

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.

Collectives™ on Stack Overflow

SQL Multiple Table Query

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Related