title: Basic SQL SUM and JOIN
body: <p>I am a bit disoriented about SQL and its associations.
  I have two tables <strong>"hotel"</strong> and <strong>"room"</strong>.</p>
  
  <p>In my <strong>room</strong> table I have a field called <strong>"hotel_id"</strong>, <strong>"capacity"</strong> and <strong>"book"</strong>
  and in my <strong>hotel</strong> table I have a field <strong>"name"</strong>.</p>
  
  <p>My <strong>"book"</strong> field is a boolean, so if it's <strong>"0"</strong> it means it is free, and if it is <strong>"1"</strong> it is booked.</p>
  
  <p>My main goal is to show the name of the hotel and the free capacity based on free rooms, like this:</p>
  
  <p><strong><em>name</em></strong></p>
  
  <p><strong>COSTA DEL SOL</strong></p>
  
  <p><strong><em>capacity</em></strong></p>
  
  <p><strong>35</strong></p>
  
  <p>(it is meant to be a table with two column)</p>
  
  <p>So I wrote this to begin with:</p>
  
  <pre><code>SELECT * FROM room WHERE book = "0";
  </code></pre>
  
  <p>which gives me all free rooms.</p>
  
  <p>Then I wrote this:</p>
  
  <pre><code>SELECT hotel_id, SUM(capacity) FROM room WHERE book = "0" GROUP BY hotel_id;
  </code></pre>
  
  <p>which gives me all capacity of all hotel with their ID's</p>
  
  <p>And then I wrote:</p>
  
  <pre><code>SELECT name, hotel_id FROM hotel INNER JOIN room ON hotel.id = room.hotel_id GROUP BY name;
  </code></pre>
  
  <p>which gives me hotel names and their ID's.</p>
  
  <p>So I'm a bit lost by combining the request of <strong>sum</strong> and <strong>join</strong>. I couldn't figure out how to make it work.</p>
  
  <p>I tried this but didn't worked out:</p>
  
  <pre><code>SELECT name, hotel_id, sum(capacity) AS hotelcapacity FROM hotel
  INNER JOIN room ON hotel.id = room.hotel_id GROUP BY hotelcapacity;
  </code></pre>
  
  <p>If anyone could help me out, it would be much appreciated. Thank you.</p>