3

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 ?

Jagmag
  • 10,283
  • 1
  • 34
  • 58
gruber
  • 28,739
  • 35
  • 124
  • 216
  • 4
    What does your real query look like? If I am not mistaken, that query will never return a null value for count, regardless of what the data looks like. Actually I can't come up with any query where `count(*)` could ever return a null value. – Guffa Nov 06 '10 at 23:48
  • problem is that I use: Insert into my table ... result of the query above. When I just use select and group by ... having COUNT(*) is null it returns 0 rows but if I insert result of query from my question I have an error message Cant insert NULL value into NumberOfConnections column – gruber Nov 07 '10 at 00:01
  • I tried an insert on a table with non-nullable field, from a select that joins agains an empty table, and I still don't get any error. I don't even get a zero value, any rows that are returned from a query like that will have a count of one or more. It will count the number of records in each group, and there can't be a group with zero records. – Guffa Nov 07 '10 at 00:30

3 Answers3

4

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
jveazey
  • 5,398
  • 1
  • 29
  • 44
  • 2
    f B.UserId is listed as NULL, then the count(* ) will return NULL, as well- this is not true – gruber Nov 07 '10 at 10:51
  • I just tried this with a couple of tables in a DB I'm working on and in such cases count(\*) still was 0, not null. Maybe I'm not reproducing the scenario accurately. But I think you're saying: If you have an outer join and their are no matching records in the outer table, then count(\*) will be null. If so, this is not correct. It's 0. – Jay Sep 15 '20 at 20:10
  • I can't seem to duplicate it now, either. I don't know if that's because of changes to SQL Server or something else. Regardless, in wacky cases, where count(*) isn't giving me what I want, I do something like this "sum(case when B.UserId is not null then 1 else 0 end)" – jveazey Sep 15 '20 at 23:06
1
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.

Mister Magoo
  • 11
  • 1
0

Ok sorry for that problem. I had also ID column which wasnt (dont know why Im sure I set this :) wasnt set to identity ON.

Then query was inserting first value as ID.

Problem is solved and I cant imagine that count(*) can return NULL

gruber
  • 28,739
  • 35
  • 124
  • 216