Wrong result for a SELECT query

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 2000 Forums

Transact-SQL (2000)

Wrong result for a SELECT query

Author

Topic

sbaroux
Starting Member

9 Posts

Posted - 2005-12-13 : 10:27:13

Hello,

I've got some issues with the IS NULL condition.

My table contains 1 column with NULL value or 1.
It contains 100 records, 40 of them have a NULL value, 60 other records have 1 as value.

select count(*) from TABLE where column = 1 => 60 - OK

select count(*) from TABLE where column is null => 100 - NOK
select count(*) from TABLE where column <> 1 => 100 - NOK

Do you have any idea why the 2 last queries return a wrong result?
How can I manage to select records which are NULL?

THanks

Stephanie

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 10:43:33

"select count(*) from TABLE where column is null"

Syntax is fine for what you want to do, please double check that you have got the TABLE and COLUMN bit correct in your code.

What do you get if you type:

SELECT TOP 10 column, * FROM TABLE WHERE column IS NULL

??

and

SELECT COUNT(*) FROM TABLE

you get 100, right? not 160??

Are you sure that "column" is NULL and not just an empty string or somesuch?

Kristen

sbaroux
Starting Member

9 Posts

Posted - 2005-12-13 : 11:04:04

Hello,

In fact, my example was not 100% accurate. I send you the complete test:

-- Result OK
select has_store_account, count(*) from store_list
group by has_store_account;
NULL - 3667226
1 - 1754859

-- result OK
select count(*) from store_list
where has_store_account = 1;
1754859

-- result NOK
-- The result is even not the total number of records ...
select count(*) from store_list
where has_store_account is NULL;
5367362 (3667226 expected)

-- result NOK
select count(*) from store_list
where has_store_account !=1;
0 (3667226 expected)

Thanks for your help

Stephanie

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 14:25:57

"select count(*) from store_list
where has_store_account !=1;
0 (3667226 expected)"

This one's easy. Your "!= 1" test will FAIL for NULL items, so it is correctly finding 0 rows. - "There are zero rows that are NOT EQUAL TO ONE, ignoring rows where the value is indeterminate [i.e. NULL]"

This one I don't understand:
"select count(*) from store_list
where has_store_account is NULL;
5367362 (3667226 expected)"

I agree, the result should be 3667226.

Is "store_list" a Table or a View? (not that I can see how that might make a difference ...)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 00:30:56

Select Count(Col) from yourTable will give you the count of col where col is not null but count(*) will will give you all


Declare @t table(id int null)
insert into @t select 24 union all select 234 union all select null union all 
select 2 union all select 1 union all select 100 union all select Null 

select count(*) from @t where id is null       --2
select count(*) from @t where id is not null   --5

select count(*) from @t                        --7
select count(Id) from @t                       --5

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources