CODEWITHSUNDEEP
sql-servert-sql
Benjo
Benjo

Reputation: 95

Count the number columns using NOT NULL in a case statement

I need some help with my query...I am trying to get a count of names in each house, all the col#'s are names.

Query:

SELECT 
    House#, 
    COUNT(CASE WHEN col#1 IS NOT NULL THEN 1 ELSE 0 END) +
    COUNT(CASE WHEN col#2 IS NOT NULL THEN 1 ELSE 0 END) +
    COUNT(CASE WHEN col#3 IS NOT NULL THEN 1 ELSE 0 END) AS count
FROM 
    myDB 
WHERE 
    House# IN (house#1, house#2, house#3) 
GROUP BY 
    House#

Desired results:

house 1 - the count is 3
house 2 - the count is 2
house 3 - the count is 1

...with my current query the results for count would be just 3's

Upvotes: 0

Views: 115

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175836

COUNT by design doesn't count NULL values.

SELECT House#, 
       COUNT(col1#) + COUNT(col2#) + COUNT(col3#) AS count
FROM myDB 
WHERE House# IN (house#1,house#2,house#3) 
GROUP BY House#;

Upvotes: 1

Related Questions