CODEWITHSUNDEEP
mysqlsql
Mexxer
Mexxer

Reputation: 1777

SQL query: Selecting an entry from one table and adding values from another

I have two tables:

hotels and hotel_rooms

I'm trying to get a table with the hotel name and the free rooms.

SELECT h.Hotelname, r.FreeRooms
FROM hotel h
INNER JOIN hotel_room r ON r.H_ID = h.H_ID
WHERE r.H_ID = h.H_ID

gives me something like

Hotel1    27
Hotel1    14
Hotel1    9
Hotel2    7
Hotel2    21

but what I actually want is to add all those values so I get:

Hotel1    50
Hotel2    28

I hope someone can help me

Upvotes: 0

Views: 89

Answers (2)

Bridge
Bridge

Reputation: 30651

Try this:

SELECT h.Hotelname,
       Sum(r.FreeRooms) as Total
FROM   hotel h
       INNER JOIN hotel_room r
         ON r.H_ID = h.H_ID
GROUP  BY h.Hotelname

Upvotes: 1

Taryn
Taryn

Reputation: 247670

You need to Sum() your results:

SELECT h.Hotelname, Sum(r.FreeRooms) as FreeRooms
FROM hotel h
INNER JOIN hotel_room r 
    ON r.H_ID = h.H_ID
GROUP BY h.Hotelname

Upvotes: 4

Related Questions