check for NULL in a column

Question

Post reply

check for NULL in a column

sql_learner29

SSCrazy Eights

Points: 9107
More actions
June 28, 2010 at 7:54 am

#226087

Hi all,
I have to form a case statement in which I have write like this:
count( case when column1 is NULL then ( column2 ) else null end) AS 'countofCOL2'
But this statement is not working as my column1 contains a null value but still it is not counting column2...
sample code
,
create table #t
(
i int
)
insert into #t(i)
values (null)
SELECT CASE i WHEN NULL THEN 'Is Null' -- This will never be returned
WHEN 0 THEN 'Is Zero' -- This will be returned when i = 0
WHEN 1 THEN 'Is One' -- This will be returned when i = 1
END
FROM #t;
any help, where am I wrong in this code...'
Thanks
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why do you need a case statement?

create table #t

(i int)

insert into #t(i) values (null)

insert into #t(i) values (1)

SELECT Count(*)

FROM #t

WHERE i IS NULL

As for the case statement you have, you're trying to do an equality check with null, that doesn't work, you need to check for nulls with IS NULL.

SELECT CASE WHEN i IS NULL THEN 'Is Null' -- This will never be returned

WHEN i = 0 THEN 'Is Zero' -- This will be returned when i = 0

WHEN i = 1 THEN 'Is One' -- This will be returned when i = 1

END

FROM #t;

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

create table #t

(

i int

)

insert into #t(i)

values (null)

SELECT CASE WHEN i IS NULL THEN 'Is Null' -- This will never be returned

WHEN i = 0 THEN 'Is Zero' -- This will be returned when i = 0

WHEN i = 1 THEN 'Is One' -- This will be returned when i = 1

END

FROM #t;

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

The simple CASE uses equivalence: CASE i WHEN NULL is the same as CASE WHEN i = NULL...

To check for NULLS you must use the searched CASE: CASE WHEN i IS NULL THEN...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

spaghettidba

SSC Guru

Points: 105732

June 28, 2010 at 8:05 am

#1185758

Sorry for echoing, Gail...

-- Gianluca Sartori

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

[withdrawn, as too many answers already :-D]

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Gianluca Sartori (6/28/2010)
Sorry for echoing, Gail...

Et tu, Gianluca.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

Thanks for quick response,

I need a case statement and need to count another column based in first column is null or not, I have many cases but this NULL case is not counting, I tried with IS NULL it is still not counting....

select

KEY

, count( case WHEN col1 IS NULL THEN col2 else null end) AS 'nullcnt'

, count( case when col1= 1 then col2 else null end) AS '1_cnt'

, count( case when col1= 2 then col2 else null end ) AS '2_cnt'

from table1

something like this , i want to achieve

Any help

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Dan.Humphries SSChampion Points: 12125 More actions · Answer 8

your case statement is not structured quite right. Try the below it should work fine.

SELECT

CASE

WHEN i is NULL THEN 'Is Null' -- This will never be returned

WHEN i=0 THEN 'Is Zero' -- This will be returned when i = 0

WHEN i=1 THEN 'Is One' -- This will be returned when i = 1

END

FROM #t;

wow we all responded at about the same time.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

thank you all it is counting fine,,

actually I was joining witha table for this col1 so it was excluding NULLS there...but now i am using coalese so it works...thanks again

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

priya__ (6/28/2010)
Thanks for quick response,
I need a case statement and need to count another column based in first column is null or not, I have many cases but this NULL case is not counting, I tried with IS NULL it is still not counting....
select
KEY
, count( case WHEN col1 IS NULL THEN col2 else null end) AS 'nullcnt'
, count( case when col1= 1 then col2 else null end) AS '1_cnt'
, count( case when col1= 2 then col2 else null end ) AS '2_cnt'
from table1
something like this , i want to achieve
Any help

Ooo!

The simplest way to get such counts - is using SUM!

select

KEY

, SUM (case WHEN col1 IS NULL THEN 1 ELSE 0 END) AS NullCNT

, SUM (case WHEN col1 = 1 THEN 1 ELSE 0 END) AS CNT_1

, SUM (case WHEN col1 = 2 THEN 1 ELSE 0 END) AS CNT_2

from table1

group by KEY

don't forget the GROUP BY!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

I would do it this way:

select

KEY

, SUM( case WHEN col1 IS NULL THEN 1 else 0 end) AS 'nullcnt'

, SUM( case when col1= 1 then 1 else 0 end) AS '1_cnt'

, SUM( case when col1= 2 then 1 else 0 end ) AS '2_cnt'

from table1

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

spaghettidba

SSC Guru

Points: 105732

June 28, 2010 at 8:22 am

#1185785

Echoing again...

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Or this way:

;WITH FastAggregate AS (

SELECT , col1, COUNT(*) AS RowCounts

FROM table1

GROUP BY , col1

)

SELECT ,

SUM(CASE WHEN col1 IS NULL THEN RowCounts END) AS 'nullcnt',

SUM(CASE WHEN col1 = 1 THEN RowCounts END) AS '1_cnt',

SUM(CASE WHEN col1 = 2 THEN RowCounts END) AS '2_cnt'

FROM FastAggregate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 14

Thanks all for your reply,

I cannot use SUM as I am counting a Key column here and if I use sum it will sum up the columne instead...

I have to use count only...

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]