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
    Helen surridge, Dec 18, 2012
    #1

  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
    FrankKalis, Dec 19, 2012
    #2

  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 :)
    Helen surridge, Dec 19, 2012
    #3

  4. FrankKalis Moderator

    Good luck with that. Hopefully it'll pay off somehow. :)
    FrankKalis, Dec 19, 2012
    #4

  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
    Madhivanan, Jan 9, 2013
    #5

Share This Page