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 column

Hi, 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:
Code:
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)
Code:
SELECT
    DISTINCT B.BuildingName, COUNT(*) OVER (partition by B.building_name)
FROM
    Buildings B
    JOIN
    Rooms R ON B.BuildingID = R.BuildingID

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |