Slappy Slappy - 2 years ago 93
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


Answer Source
SELECT H.hotelName, SUM(R.roomPrice) as 'Room Price'
FROM hotel H
JOIN room R
ON H.hotelNo = R.hotelNo
GROUP BY H.hotelNo;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download