title: query to Get sum of two different items that is mapped onto same code
body: <p>I have this Inventory table for Rooms :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">dtm</th>
  <th style="text-align: center;">hotelid</th>
  <th style="text-align: right;">roomcode</th>
  <th style="text-align: right;">intqty</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2000-01-04</td>
  <td style="text-align: center;">23</td>
  <td style="text-align: right;">svr</td>
  <td style="text-align: right;">9</td>
  </tr>
  <tr>
  <td style="text-align: left;">2000-01-04</td>
  <td style="text-align: center;">23</td>
  <td style="text-align: right;">ovb</td>
  <td style="text-align: right;">9</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>this is my mapping table :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">hotelid</th>
  <th style="text-align: center;">roomcode1</th>
  <th style="text-align: right;">roomcode2</th>
  <th style="text-align: right;">externalroom</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">23</td>
  <td style="text-align: center;">svr</td>
  <td style="text-align: right;">ovb</td>
  <td style="text-align: right;">023</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I am trying to get the sum of intqty on the inventory table above for both room on same date:
  This is what i have so far :</p>
  <pre><code>select
      a.dtm as InvDate,
      a.intResortID as ResortId,
      a.strRoomType as RoomType,
      sUM(a.intQty) as InvCount,
      am.externalRoomId
  from
      dbo.tblAvailable c
      join dbo.tblAvailable a on a.dtm = c.dtm
         and a.intResortID = c.intResortID
         and a.strRoomType = c.strRoomType
      join dbo.tblResorts r on r.intResortID = a.intResortID
      join bbtest.externalResortMap arm on arm.ResortID = a.intResortID
      join bbtest.externalRoomMap am on am.RoomId = a.strRoomType
  group by
      a.dtm,
      a.intResortID,
      a.strRoomType,
      a.intQty,
      am.externalRoomId
  order by
      invDate;
  </code></pre>
  <p>The expected result is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Invdate</th>
  <th style="text-align: center;">ResortId</th>
  <th style="text-align: center;">RoomType</th>
  <th style="text-align: center;">InvCount</th>
  <th style="text-align: right;">externalRoomId</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2000-01-04</td>
  <td style="text-align: center;">23</td>
  <td style="text-align: center;">svr</td>
  <td style="text-align: center;">18</td>
  <td style="text-align: right;">023</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Query output :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Invdate</th>
  <th style="text-align: center;">ResortId</th>
  <th style="text-align: center;">RoomType</th>
  <th style="text-align: center;">InvCount</th>
  <th style="text-align: right;">externalRoomId</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2000-01-04</td>
  <td style="text-align: center;">23</td>
  <td style="text-align: center;">svr</td>
  <td style="text-align: center;">9</td>
  <td style="text-align: right;">023</td>
  </tr>
  <tr>
  <td style="text-align: left;">2000-01-04</td>
  <td style="text-align: center;">23</td>
  <td style="text-align: center;">ovb</td>
  <td style="text-align: center;">9</td>
  <td style="text-align: right;">023</td>
  </tr>
  </tbody>
  </table>
  </div>