Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #702 > unrolled thread

SQL help again

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  SQL help again "Phil Hunt" <aaa@aaa.com> - 2011-10-02 12:14 -0400
    Re: SQL help again "Phil Hunt" <aaa@aaa.com> - 2011-10-02 12:48 -0400
      Re: SQL help again "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-10-02 13:06 -0400
        Re: SQL help again "phil hunt" <a@abc.com> - 2011-10-02 18:44 -0400
        Re: SQL help again "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-10-03 12:09 -0400
    Re: SQL help again --CELKO-- <jcelko212@earthlink.net> - 2011-10-03 08:55 -0700

#702 — SQL help again

How do I count a column that is NULL without CASE. I was looking at NULLIF 
and ISNULL, they can't seem to do the job.

Thanks

Sorry about double posting, I replied to my question by mistake. 

[toc] | [next] | [standalone]

#703

I tried
CASE EventID when NULL then 1 else 0 end

It always return 0, why ?


"Phil Hunt" <aaa@aaa.com> wrote in message 
news:j6a2kf$3d5$1@speranza.aioe.org...
> How do I count a column that is NULL without CASE. I was looking at NULLIF 
> and ISNULL, they can't seem to do the job.
>
> Thanks
>
> Sorry about double posting, I replied to my question by mistake.
> 

[toc] | [prev] | [next] | [standalone]

#704

Phil Hunt wrote:
> I tried
> CASE EventID when NULL then 1 else 0 end
>
> It always return 0, why ?
>
>
Because the way you have the statement structured is causing an equality 
comparison to be performed, the result of which will always be false. You 
have to structure it like:

CASE when  EventID IS NULL then 1 else 0 end

To answer your original question, you can subtract the result of 
count(distinct column_name) from count(*):

select count(*) - count(distinct eventid) as null_eventids

[toc] | [prev] | [next] | [standalone]

#720

 That's it.Thanks.


"Bob Barrows" <reb01501@NOSPAMyahoo.com> wrote in message 
news:j6a5n5$9d3$1@dont-email.me...
> Phil Hunt wrote:
>> I tried
>> CASE EventID when NULL then 1 else 0 end
>>
>> It always return 0, why ?
>>
>>
> Because the way you have the statement structured is causing an equality 
> comparison to be performed, the result of which will always be false. You 
> have to structure it like:
>
> CASE when  EventID IS NULL then 1 else 0 end
>
> To answer your original question, you can subtract the result of 
> count(distinct column_name) from count(*):
>
> select count(*) - count(distinct eventid) as null_eventids
>
>
>
> 

[toc] | [prev] | [next] | [standalone]

#725

Bob Barrows wrote:
> To answer your original question, you can subtract the result of
> count(distinct column_name) from count(*):
>
> select count(*) - count(distinct eventid) as null_eventids

Oh dear. Why did I put that "distinct" in there? I have no idea what I was
thinking when I typed that. The correct statement is as CELKO wrote:
select count(*) - count(eventid) as null_eventids

[toc] | [prev] | [next] | [standalone]

#723

SELECT COUNT(*)- COUNT(event_id) AS null_even_cnt
FROM Events;

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web