How to count number of rows against associated with another column | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
How to count number of rows against associated with another columnHi, I have 2 tables (SQL Server 2008) , one for buildings and one for rooms. The building table gives the name and the id no, the room table gives the building id and the individual rooms. For example building 1 (ID 1) can have Rooms A, B, C & D, building 2 (ID 2) has Rooms E, F, G etc. How can I write a query that gives me the output Building 1 – No of Rooms = 4, Building 2 – No of Rooms = 3 etc? Many thanks
Welcome to the forum!
Is this an actual problem, or just a homework assignment?
Looks like you’re looking for something like:
SELECT B.BuildingName, COUNT(*) FROM Buildings B JOIN Rooms R ON R.BuildingID = R.BuildingID GROUP BY B.BuildingName
Hi Frank, I wish I was young enough to have a homework assignment! A genuine problem I was asked to do. Thank you so much it worked! Just what I was looking for. I can now impress the boss! Thanks again
Good luck with that. Hopefully it’ll pay off somehow.
In Frank’s code, Rooms R ON R.BuildingID = R.BuildingID should be Rooms R ON B.BuildingID = R.BuildingID Another method (But may not be effecient)
SELECT DISTINCT B.BuildingName, COUNT(*) OVER (partition by B.building_name) FROM Buildings B JOIN Rooms R ON B.BuildingID = R.BuildingID