counting numbers of occurences of null

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

General SQL Server Forums

New to SQL Server Programming

counting numbers of occurences of null

Author

Topic

mashfield
Starting Member

7 Posts

Posted - 2005-09-20 : 07:59:12

Hi All,

I have a table with (lets say) 2 fields: ACTIONED_TO & COMMENTS

There will be multiple rows with the same ACTIONED_TO value some of the COMMENTS will be null

eg:

ACTIONED_TO,COMMENTS
"Matt","This is a comment"
"Dave","This is a comment too"
"Matt",

what I'm looking for my query to return is:

ACTIONED_TO, NO COMMENTS, COMMENTS
MATT, 1,1
Dave,1,0

Do I need a CROSSTAB Query, How is it implemented?

Regards

Matt A

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-20 : 08:31:36

select actioned_to, sum(case (comments) when not null then 1 end), sum(case (comments) when null then 1 end)
from yourtable
group by actioned_to

will be close........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-20 : 09:56:12

quote:
Originally posted by AndrewMurphy

select actioned_to, sum(case (comments) when not null then 1 end), sum(case (comments) when null then 1 end)
from yourtable
group by actioned_to

will be close........

Slight change

select actioned_to, sum(case (comments) when not null then 1 else 0 end), sum(case (comments) when null then 1 else 0 end)
from yourtable

so that No counts have 0 instead of Null

Madhivanan

Failing to plan is Planning to fail

mashfield
Starting Member

7 Posts

Posted - 2005-09-20 : 10:33:14

Nearly there folks, However

the word NOT as in NOT NULL is not liked.

PS Using VS2005 and building a web site of off SQL2000

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-09-20 : 11:23:58

[code]
sum(case when comments is not null then 1 else 0 end), sum(case when comments is null then 1 else 0 end)
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-20 : 11:24:04

This should do it:

select
	actioned_to,
	[NO COMMENTS] =
	sum(case when comments is null then 1 else 0 end),
	[COMMENTS]
	sum(case when comments is null then 0 else 1 end) 
from
	yourtable
group by
	actioned_to
order by
	actioned_to

CODO ERGO SUM

mashfield
Starting Member

7 Posts

Posted - 2005-09-20 : 11:49:54

Hi All,

Tried WHEN NULL THEN 0 ELSE 1 END & WHEN NULL THEN 1 ELSE 0 END

however, both columns return the same data

I am clear that the colums should be diffent.

Ahhhhhhh

mashfield
Starting Member

7 Posts

Posted - 2005-09-20 : 12:23:31

ok, so i was using count instead of sum...now when I use sum 1 get 1 column with all the records and 1 column with 0

SELECT rca_review_by, SUM(CASE (rca_root_cause) WHEN NULL THEN 1 ELSE 0 END) AS Expr1, SUM(CASE (rca_root_cause) WHEN NULL THEN 0 ELSE 1 END) AS Expr2 FROM dbo.BugTracking GROUP BY rca_review_by HAVING (NOT (rca_review_by IS NULL)) ORDER BY rca_review_by

Please help somebody!!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-20 : 19:25:30

I answered your question already. Just to make it clear, your are using the wrong form of CASE. Did you try what I posted? Why are the column names different in you latest post?

quote:
Originally posted by mashfield

ok, so i was using count instead of sum...now when I use sum 1 get 1 column with all the records and 1 column with 0

SELECT rca_review_by, SUM(CASE (rca_root_cause) WHEN NULL THEN 1 ELSE 0 END) AS Expr1, SUM(CASE (rca_root_cause) WHEN NULL THEN 0 ELSE 1 END) AS Expr2 FROM dbo.BugTracking GROUP BY rca_review_by HAVING (NOT (rca_review_by IS NULL)) ORDER BY rca_review_by

Please help somebody!!!

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-20 : 20:20:59

>>(from ~~MJV~~ MVJ) you're are using the wrong form of CASE.

Here is something that is definately NOT too obvious about nulls and Simple CASE compared with Searched CASE:


select	col
	,case when col is null then 0 else 1 end	as [SearchedCASE]
	,case col when null then 0 else 1 end 		as [SimpleCASE]
from	(
	select 1 col	union all
	select null 	union all
	select 2 	union all
	select null 	union all
	select 3 	union all
	select null
	) a

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-20 : 22:52:16

That's why I posted an answer with the other form of case.

I almost always use this form of CASE statement:
case
when condition1 is true
then value1
when condition2 is true
then value2
else value3
end

I hardly ever use this form of CASE statement:
case value
when value1
then value2
when value3
then value4
else value5
end

quote:
Originally posted by TG
..Here is something that is definately NOT too obvious about nulls and Simple CASE compared with Searched CASE...

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 00:31:13

But I think both form will work correctly although first is more readable

Madhivanan

Failing to plan is Planning to fail

mashfield
Starting Member

7 Posts

Posted - 2005-09-21 : 04:31:14

Hi People,

I now have my Query Working! Many Thanks to all.

Michael, The latest post from me was direcly from my script, using the real row names.

All, I may be coming back for additional assistance as I'm new to SQL, so please bear with me.

Thanks again

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-21 : 07:25:53

I don't think this form will work for this problem:
case value
when value1
then value2
when value3
then value4
else value5
end

The point is that NULL never equals NULL, so the following will alway evaluate to zero.
case comments
when NULL
then 1
else 0
end

This simple test will show you what I mean:
select result = case null when null then 1 else 0 end

result
-----------
0

(1 row(s) affected)

quote:
Originally posted by madhivanan

But I think both form will work correctly although first is more readable

Madhivanan

Failing to plan is Planning to fail

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 07:43:15

Well
It returns Null instead of 0

Declare @t table(i int)
insert into @t values(Null)
insert into @t values(3)
insert into @t values(456)
Select case i when Null then 0 else i end from @t
Select case when i is Null then 0 else i end from @t

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-21 : 08:09:49

quote:
Originally posted by madhivanan

Well
It returns Null instead of 0

Declare @t table(i int)
insert into @t values(Null)
insert into @t values(3)
insert into @t values(456)
Select case i when Null then 0 else i end from @t
Select case when i is Null then 0 else i end from @t

Madhivanan

Failing to plan is Planning to fail

If you are trying to just return a 0 or 1 based on null or not null then that form has a problem:


select	SimpleCASE
	,count(SimpleCASE) [counts]
from	(
	select	col
		,case when col is null then 0 else 1 end	as [SearchedCASE]
		,case col when null then 0 else 1 end 		as [SimpleCASE]
	from	(
		select 1 col	union all
		select null 	union all
		select 2 	union all
		select null 	union all
		select 3 	union all
		select null
		) a
	) a
group by SimpleCASE


select	SearchedCASE
	,count(SearchedCASE) [counts]
from	(
	select	col
		,case when col is null then 0 else 1 end	as [SearchedCASE]
		,case col when null then 0 else 1 end 		as [SimpleCASE]
	from	(
		select 1 col	union all
		select null 	union all
		select 2 	union all
		select null 	union all
		select 3 	union all
		select null
		) a
	) a
group by SearchedCASE

>>(from MVJ): That's why I posted an answer with the other form of case. I almost always use this form of CASE statement:

Me too.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 08:17:12

Well. I also prefer to use Case when col is null then .. method but didnt realise there is this much difference

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources