Counting NULL columns?

If I had a basic table with 3 VARCHAR fields; let's say A, B, C

How could I write a query that returns the count of the number of NULL columns for every record in a table?

Ideally, it would be something like:

SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable

That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:

select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right

the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls

3
2
0
1
0
0
0
2
0
3
0
2
1

at least my query actually produces something useful

:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D

And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.

... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:

select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable

Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.

Thanks Rudy and blindman!

FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern

suppose i had a Three Stooges table with this row:

'curly','larry',null

you're saying it's okay to overwrite this with

'tom','dick','harry'

but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:

Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey

This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!

-PatP|||Only when you are in the dark...|||nice

might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?