SQL Server Performance

How to count number of rows against associated with another column

Discussion in 'ALL SQL SERVER QUESTIONS' started by Helen surridge, Dec 18, 2012.

  1. Helen surridge New Member

    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
  2. FrankKalis Moderator

    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
  3. Helen surridge New Member

    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 :)
  4. FrankKalis Moderator

    Good luck with that. Hopefully it'll pay off somehow. :)
  5. Madhivanan Moderator

    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

Share This Page