Search code examples
sqlsql-servert-sqlsql-server-2012analytic-functions

Count over Partition by with one condition (/don't count the NULL values)


I want to count how many houses are within a building. Dataset like the following:

BuildingID, HouseID
1, 1
1, 2
1, 3
2, 4
2, 5
2, 6
NULL, 7
NULL, 8

With the following code it shows the total count of the houses, however, houses 7 and 8 don't have a building, so it shouldn't count anything.

SELECT BuildingID
     , HouseID
     , COUNT(HouseID) OVER (PARTITION BY BuildingID) AS 'Houses in Building'
FROM BUILDING

The result I get:

BuildingID, HouseID, Houses in Building
1, 1, 3
1, 2, 3
1, 3, 3
2, 4, 3
2, 5, 3
2, 6, 3
NULL, 7, 2
NULL, 8, 2

The result I want:

BuildingID, HouseID, Houses in Building
1, 1, 3
1, 2, 3
1, 3, 3
2, 4, 3
2, 5, 3
2, 6, 3
NULL, 7, NULL --or 0
NULL, 8, NULL --or 0

Any suggestions?


Solution

  • Just count the BuildingID. The COUNT function does not count nulls so it'll work:

    COUNT(BuildingID) OVER (PARTITION BY BuildingID) AS 'Houses in Building'
    

    Note that it assumes that HouseID is not null.