Tharwen Tharwen - 3 months ago 10
SQL Question

Getting an average for each row based on a separate query

Table schema

Above is my table schema. My task is to Write a SQL command to display for each publisher the publisher’s name, the publisher’s location and the
average cost of the books that the publisher sells.
I have a mostly working query:

SELECT Publisher.name, Publisher.location,
(SELECT AVG(Book.cost)
FROM (Book
INNER JOIN Publisher
ON Book.publisherName = Publisher.name)
WHERE Book.publisherName = Publisher.name
) bookAverage FROM Book
INNER JOIN Publisher ON Book.publisherName = Publisher.name;


The problem is that this returns the average of all books in the
Books
table. How can I change this to only return the average cost of the books associated with each publisher?

Here's a fiddle with the schema implemented already:

http://sqlfiddle.com/#!9/7a9909/11/0

Answer
SELECT p.name, p.location, AVG(b.Cost) as AverageBookCost
 FROM
   Publisher p
   INNER JOIN book b
   ON b.publisherName = p.name
GROUP BY
  p.name, p.location

http://sqlfiddle.com/#!9/7a9909/18

Only 1 join, no sub queries, inner selects nothing needed as you are looking for the straight forward aggregate of the join between the tables. Simply specify you GROUP BY clause correctly.