Subquery in the FROM Statement

Other SQL Server Topics
1

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

Thanks

2

Maybe this line of thinking is what you need?

SELECT TOP 1 buildingNo, SUM(roomPrice) AS [Total Room Cost]
FROM MyTable
GROUP BY buildingNo
ORDER BY [Total Room Cost] DESC
3

I would include the roomNo, so you know which room it was that had the highest cost:

SELECT TOP (1) buildingNo, roomNo, SUM(roomPrice) AS [Total Room Cost]
FROM dbo.table_name
GROUP BY buildingNo, roomNo
ORDER BY SUM(roomPrice) DESC
4

I probably misunderstood, but my reading was to find the Building with the biggest total of all the rooms' prices ...

Not taking any bets though!

5

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.

6

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
1 Like