Weirdness in COUNT(*) WHERE .... IS NULL 1

[mkrausnick]

I tried to count records using IS NOT NULL in the WHERE clause, then using IS NULL in the where clause, then no where clause, and the sum of the first two counts do not equal the third count. How is this possible?

Background

I have a 7.5 million record transaction table to which I added a field ADDRID as char(10). Then I populated the field from another field in the table based on a condition:

update xact set ADDRID=left(OTHERFIELD,10) WHERE OTHERFIELD IS NOT NULL AND LEFT(OTHERFIELD,1) = '_'

To check the result, I counted the records by fiscal year:

select fy,count(*) from xact group by fy

The relevent results are:
2001-2002: 952,358
2002-2003: 972,257

Then I issued the query:

select fy,count(*) from xact where addrid IS NULL group by fy

The relevent results are:
2001-2002: 952,350
2002-2003: 921,464

Lastly, I issued the query:

select fy,count(*) from xact where addrid IS NOT NULL group by fy

The relevent results are:
2001-2002: 861,209
2002-2003: 972,257

How can records be counted as both NULL and NOT NULL at the same time?

Mike Krausnick
Dublin, California

 

[gmmastros]

What results do you get with this query...

Select fy,
       Sum(Case When AddrId Is NULL Then 1 Else 0 End) As NullCount,
       Sum(Case When AddrId Is NULL Then 0 Else 1 End) As NotNullCount,
       Count(1) As TotalCount
From   xact
Group By fy

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mkrausnick]

Interesting. George's clever query yields:
[tt]

fy NullCount NotNullCount TotalCount
--------- --------- ------------ ----------
2001-2002 91,149 861,209 952,358
2002-2003 0 972,257 952,257
[/tt]

Which tells me what I need to know, i.e., all the 2002-2003 records were correctly updated, but WHY????

A star for the query, George, thanks!

Mike Krausnick
Dublin, California

 

[dk87]

Rather than a count(*) in the original, does count(AddrId) yield different results?

 

[hneal98]

I would usually use

Where isnull(FieldName,'X')<> 'X'

 

[marcopipino]

Yes, I too use

Where isnull(FieldName,'X')<> 'X'

bur not in this case,
George has wrote the right query

Marco

http://www.geocities.com/marcopipino

 

[hneal98]

you are correct. I wasn't paying attention. :-(