mysql - SQL query, AVG and COUNT on multiple tables -

I need a query, the query I've used for some reason is the way I want it Does not

have all the tables included in the query.

Here's the query Which I want: Show a list of books with the number of their average rating and its recommendations

The result should be:

I have already tried:

  SELECT book.isbn , [Average rating] as AVG (ratings.rating), [number of recommended] in the form of COUNT (recommend.isbn) INNER from the book Include in book.isbn = recommend.isbn Get ratings on the INNER book I Isbn = Rates.isbn GROUP BY book.isbn  

But this did not work, AVG rating works great Does not, but # of recommendations, it is not the struggle with the rating table.

Here's the result:

and for # recommendations:

  SELECT book.isbn, COUNT (recommend.isbn) AS [Number of recommended] questions Join INNER from a book.isbn = recommend.isbn group by book.isbn  

Here is the result:

So I want a query tie two scenes in one view

Well, you can combine two ideas .. ..

Here, You should work the way you like

  SELECT l.isbn, v.rating, r.rec from book l, (SELECT isbn, AVG (Rating) Rating Rating ISBN by group recommended by REC as SELECT isbn, COUNT (isbn) by group ISBN), v. ISBN and l.isbn = r.isbn  

P> Hope this helps.

Best regards!