Count Problem 2

[dbomrrsm]

I have a table with one column with 48251 rows in all with distinct id's so:

select count (distinct event_number)
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS

gives the reults 48251

I then do:

select count (distinct event_number)
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS
where event_number not in 
(select event_number from zbisiebel_sr_act_ccc)

which returns a result of 0.

This indicates to me that all event numbers in the first table can be found in the second.

I then do:

select count (distinct event_number)
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS
where event_number in 
(select event_number from zbisiebel_sr_act_ccc)

that gives a reult of 38207 when I was expecting 48251 i.e. all of them are in the second table.

I have also tried:

select count (distinct t1.event_number)
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS t1
left outer join zbisiebel_sr_act_ccc t2
on t1.event_number = t2.event_number
where t2.event_number is not null

and this gives the result of 38207.

Can anyone explain this ?




[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[monksnake]

I then do:

select count (distinct event_number)
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS
where event_number in 
(select event_number from zbisiebel_sr_act_ccc)

that gives a reult of 38207 when I was expecting 48251 i.e. all of them are in the second table.

You say you were expecting 48251 in that 3rd query down. That could only be the case if the table zbisiebel_sr_act_ccc had exactly 48251 distinct event numbers based in the other queries you have.

That query above tells me that zbisiebel_sr_act_ccc contains 38207 different event numbers
since it will only list event numbers that are in the table
zbisiebel_sr_act_ccc. It just so happens that all 38207 event numbers in the second table are also all in the first table as shown by this query :

select count (distinct event_number)
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS
where event_number not in 
(select event_number from zbisiebel_sr_act_ccc)

I hope I didn't lose you. My explanation probably isn't very clear.


<.

 

[AlexCuse]

DBomrrsm -

What does this query get you, out of curiousity:

select count(a.event_number) from 
(select distinct event_number
from ZBI_SIEBEL_CUST_CONTACT_OS_ITEMS) a

I am not very familiar with count distinct, but I wonder if your problem might be in there.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[r937]

any NULLs in either table?

r937.com | rudy.ca

 

[SQLDenis]

you have null values in your table use NOT EXISTS or a lEFT JOIN instead of NOT IN

read this

http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[dbomrrsm]

Denis and Rudy

Spot on with the nulls - thanks.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]