Tharwen Tharwen - 1 year ago 67
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.location,
(SELECT AVG(Book.cost)
FROM (Book
INNER JOIN Publisher
ON Book.publisherName =
WHERE Book.publisherName =
) bookAverage FROM Book
INNER JOIN Publisher ON Book.publisherName =;

The problem is that this returns the average of all books in the
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:!9/7a9909/11/0

Answer Source
SELECT, p.location, AVG(b.Cost) as AverageBookCost
   Publisher p
   INNER JOIN book b
   ON b.publisherName =
GROUP BY, p.location!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.