CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012analytic-functions
titatovenaar
titatovenaar

Reputation: 309

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?

Upvotes: 0

Views: 4467

Answers (4)

techkrg
techkrg

Reputation: 219

You can use case when condition in Count function like below,

COUNT(CASE WHEN BuildingID IS  NOT NULL THEN HouseID END) OVER (PARTITION BY BuildingID)  AS 'Houses in Building'

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

You can check this following self join option-

WITH your_table (BuildingID, HouseID)
AS
(
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT NULL, 7 UNION ALL
SELECT NULL, 8
)

SELECT A.BuildingID,A.HouseID,COUNT(A.BuildingID)Count
FROM your_table A
LEFT JOIN your_table B ON A.BuildingID = B.BuildingID
GROUP BY A.BuildingID,A.HouseID

Output is-

BuildingID  HouseID Count
1           1       3
1           2       3
1           3       3
2           4       3
2           5       3
2           6       3
NULL        7       0
NULL        8       0

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272046

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.

Upvotes: 2

GarethD
GarethD

Reputation: 69749

You could simply use a case expression to only show a count where the BuildingID is not null, or you could change your count to be COUNT(BuildingID) rather than COUNT(HouseID) (Since COUNT(NULL) gives 0). Both yield your required results:

DECLARE @Building TABLE (BuildingID INT, HouseID INT);
INSERT @Building (BuildingID, HouseID)
VALUES 
    (1, 1), (1, 2), (1, 3), (2, 4), (2, 5), 
    (2, 6), (NULL, 7), (NULL, 8);

SELECT  BuildingID,
        HouseID,
        CountBuildingID = COUNT(BuildingID) OVER (PARTITION BY BuildingID),
        CaseExpression = CASE WHEN BuildingID IS NOT NULL THEN COUNT(HouseID) OVER (PARTITION BY BuildingID) END
FROM    @Building
ORDER BY HouseID;

OUTPUT

BuildingID   HouseID    CountBuildingID     CaseExpression
-------------------------------------------------------
1               1           3                   3
1               2           3                   3
1               3           3                   3
2               4           3                   3
2               5           3                   3
2               6           3                   3
NULL            7           0                   NULL
NULL            8           0                   NULL

Upvotes: 0

Related Questions