To find NULL count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

To find NULL count

Author

Topic

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-29 : 08:27:51

Hi ,
My table is like this

id [1] [2] [3] [4] [5] NOTBOOKED
1 8 NULL 7 NULL NULL ?
2 NULL 9 NULL 5 7 ?
3 4 NULL 5 NULL 2 ?

Hope you understand,
Actually I want to count how many NULL for id 1,2,3 to fill it up
in NOTBOOKED column
Plz suggest

------------------------------------------------------
"Desire makes what you wants to do"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-29 : 08:41:15

update t1
set NOTBOOKED = dt.NULLCount
from your_table as t1
join
(
select
id,
case when [1] is null then 1 else 0 end +
case when [2] is null then 1 else 0 end +
case when [3] is null then 1 else 0 end +
case when [4] is null then 1 else 0 end +
case when [5] is null then 1 else 0 end as NULLCount
from your_table
)dt
on t1.id = dt.id

No, you're never too old to Yak'n'Roll if you're too young to die.

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-29 : 09:36:06

can you suggest me shorter query becoz am using from 1 to 31...it is hard to manually type one by one

------------------------------------------------------
"Desire makes what you wants to do"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-29 : 09:41:27

It isn't that hard.
Just type one row and then copy it and then paste it 30 times and then you have only to replace the column name...

I am sorry but I have no other solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources