Slappy Slappy - 6 months ago 22
MySQL Question

SQL sub query to retrieve total cost for each hotel

I'm trying to get my head around sub queries and I'm certain I don't actually know what I'm doing. I've got some code that pulls up the hotel names and room price, but the prices are showing the absolute total room price for all hotels together, not each hotel seperately.

SELECT hotelName, SUM(roomPrice) AS 'Room Price'

FROM hotel, room

GROUP BY hotelName


This code gives me this

Hotel Name and Individual total room cost

2360 is the total room cost over every hotel, I just need to change it to show the cost of each hotels total rooms, individually.

EDIT: Added a image of the database relations

DATABASE

Answer
SELECT H.hotelName, SUM(R.roomPrice) as 'Room Price'
FROM hotel H
JOIN room R
ON H.hotelNo = R.hotelNo
GROUP BY H.hotelNo;