Count(*) returns null

Question

I have call details records table. Each row has got UserId and UserBNumber.

I also have got table which has UserId and Number so that having number I can say what is ID of the user.

So that from each CDR record I can say what is an ID of one who calls an one who receives call.

Sometimes user calls to number which isnt in my database of users (calls outside the network)

Now I would like to have query which gives me UserAId(caller), UserBId(receiver), count(*)

So that I would know number of connections between users.

UserAId, UserBId, NumberOfConnections

If number who receives call isnt in my table then UserA calls someone outside the network.

I would like to have in results:

UserAId, NULL, NumberOfConnectionsOutsideTheNetwork

here is my query:

TableA: CDR Table
TableB: User -> Number table

select A.UserId, B.UserId, count(*)
from select tableA A 
left outer join tableB B
on A.UserBNumber = B.Number
group by A.UserId, B.UserId   

problem is that I Count(*) sometimes returns NULL.

What am i doing wrong ?

Answer 1

Your query is performing an outer join while trying to perform a count. If B.UserId is listed as NULL, then the count(* ) will return NULL, as well. You can fix this by explicitly performing a count of A using "count (A.*)" or by wrapping it in ISNULL().

select A.UserId, B.UserId, count(A.*)
  from select tableA A 
  left outer join tableB B
    on A.UserBNumber = B.Number
 group by A.UserId, B.UserId   

or

select A.UserId, B.UserId, isnull(count(*),0)
  from select tableA A 
  left outer join tableB B
    on A.UserBNumber = B.Number
 group by A.UserId, B.UserId   

Answer 2

select A.UserId, COALESCE(B.UserId,'NumberOfConnectionsOutsideTheNetwork') AS UserId_B, count(*)
from select tableA A 
left outer join tableB B
on A.UserBNumber = B.Number
group by A.UserId, COALESCE(B.UserId,'NumberOfConnectionsOutsideTheNetwork')  

Try that - you will never group on a null this way.