T-SQL (SS2K8) :: Select Statement That Needs To Use Custom Grouping

I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:

SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code

The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.

For example:

Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365

Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.

Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.

I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.

People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…

The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.