Tharwen Tharwen - 1 year ago 79
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 Source
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download