I am very new to the SQL coding and I am struggling with this query.
I need to retrieve the MAX roomPrice AS "Total Room Cost" in a building chain grouped by buildingNo and the subquery forms part of the FROM statement.
The table consists of roomNo(pk), buildingNo(fk), roomSize, roomPrice
Each building has a number of rooms so I know I need to get the SUM of those before I can do the MAX.
I know it sounds easy but for me just learning it has been doing my head in.
Any help would be much appreciated
You are right Kristen and after a quick talk with the head of my department that is suppose to be teaching me all I need is the the building with the max total of their rooms. I replied that it was so untidy with one value and you do not know what building it was from. He also said that it is the subquery that is the key to this outcome. This is what he has written down for another person.
SELECT MAX(totalRoomCost) FROM (SELECT SUM(roomPrice) AS totalRoomCost
FROM room GROUP BY buildingNo) AS maxTotalRoomCost
To me this is so damn messy and as I said it just returns the one value and no building number. I would like to change it to have the building number.
The SubQuery is fine, but these days I just use the TOP (1) syntax for that - because it allows be to pull any columns I want from the resultset (e.g. buildingNo), rather than just getting the MAX value.
It also "scales" well to "What are the buildings with the 5 highest room rates" etc.
So, leaving out roomNo, my revised query would be:
SELECT TOP (1) buildingNo, SUM(roomPrice) AS [Total Room Cost]
FROM dbo.room
GROUP BY buildingNo
ORDER BY SUM(roomPrice) DESC