January 17, 2008 at 3:34 pm
I have the following SQL statement:
SELECT COL1, COL2, COL3, COL4, COL5 FROM TableA WHERE ID = 3
I want to know how many of these 5 columns return NULL (just the count).
Any idea how can I do that? Using Coalesce only bring me the first non-NULL value not the count.
Thanks in advance.
sg2000
January 17, 2008 at 3:40 pm
Try this trick on:
select count(*)-count(col1) as Col1Blank,
count(*)-count(col2) as Col2Blank,
count(*)-count(col3) as Col3Blank,
count(*)-count(col4) as Col4Blank,
count(*)-count(col5) as Col5Blank
from mytable
where id=3
Count returns the count of non-null values in a specific column, unless used with * which will count each row regardless of nulls.
EDit - Just change that to be the correct term - non-null. "blank" had nothing to do with it... Not sure where that came from....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 4:14 pm
If you are going horizontally instead of vertically and they are all numeric, you could try something like this
SELECT ISNULL(col1-col1, 1) + ISNULL(col2-col2,1)+...
If they are not numeric, you could do something like this:
SELECT CASE WHEN col1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN col2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN col3 IS NULL THEN 1 ELSE 0 END +...
not pretty, but it will work for any datatype
January 18, 2008 at 9:54 am
Thank you all, I got it working using the COUNT function as it is simpler.
sg2000
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply