title: SQL query, AVG and COUNT on multiple tables
body: <p>I need a query, the query that i used doesn't work the way i want for some reason</p>
  
  <p>Here's all the tables involved in the query.</p>
  
  <p><img src="https://i.imgur.com/prLxUNR.png" alt="sql tables for the query"></p>
  
  <p><strong>Here's the query i want :</strong>
  Show a list of books with their average ratings and its number of recommendations</p>
  
  <p>result should be like this :
  <img src="https://i.stack.imgur.com/TSD9J.png" alt="result of query"></p>
  
  <p><strong>What i already tried :</strong></p>
  
  <pre><code>SELECT        book.isbn, AVG(ratings.rating) AS [AVG Ratings], COUNT(recommend.isbn) AS [Number of recommendation]
  FROM            book INNER JOIN
                           recommend ON book.isbn = recommend.isbn INNER JOIN
                           ratings ON book.isbn = ratings.isbn
  GROUP BY book.isbn
  </code></pre>
  
  <p>But it didn't work, somehow the the AVG rating works great, but the # of recommendations does not, it conflicts with the ratings table.</p>
  
  <p>here's what the result is:</p>
  
  <p><img src="https://i.stack.imgur.com/vP4IX.png" alt="enter image description here"></p>
  
  <p>However when i try each one alone, everything works great like this :</p>
  
  <p>for AVG ratings :</p>
  
  <pre><code>SELECT        book.isbn, AVG(ratings.rating) AS [AVG Ratings]
  FROM            book INNER JOIN
                           ratings ON book.isbn = ratings.isbn
  GROUP BY book.isbn
  </code></pre>
  
  <p>Here's the result :</p>
  
  <p><img src="https://i.stack.imgur.com/N9RGP.png" alt="enter image description here"></p>
  
  <p>And for the # of recommendations :</p>
  
  <pre><code>SELECT        book.isbn, COUNT(recommend.isbn) AS [Number of recommendation]
  FROM            book INNER JOIN
                           recommend ON book.isbn = recommend.isbn
  GROUP BY book.isbn
  </code></pre>
  
  <p>Here's the result : </p>
  
  <p><img src="https://i.stack.imgur.com/AVOZM.png" alt="enter image description here"></p>
  
  <p><strong>So i want a query to combine the two views into one view</strong></p>