ISNULL(Count(x),0) not returning 0 when null

Question

I have the following code:

SELECT     FirstName, LastName,
(SELECT ISNULL(COUNT(UP1.EmailAddress), 0) AS HasEmail 
     From dbo.UserProfiles AS UP1 
     WHERE (NOT (UP1.EmailAddress IS NULL)) AND (CreatedBy = dbo.UserProfiles.UserID)
     GROUP BY CreatedBy) AS EmailEnteredCount FROM dbo.UserProfiles WHERE (IsStaff = 1)

Sample Results:

LastName EmailEnteredCount

bill NULL

Larson 51

Christie 30

parrish NULL

senac NULL

The code executes correctly with one exception, it is returning a null value when no records are found rather than the intended 0. I have also tried using coalesce to no avail.

UPDATE: This returns what I am trying to accomplish just need a cleaner solution. I really don't think I need to create a temp table just to replace a null value.

drop table #tt
                    select userid,firstname, lastname, 
                    (
                      SELECT     count(*) AS HasEmail
                      FROM         dbo.UserProfiles AS UP1
                      WHERE     (UP1.EmailAddress IS NOT NULL)AND (UP1.CreatedBy = UserProfiles.UserId) and (datecreated between @startdate and @enddate)
                      GROUP BY CreatedBy
                    ) as EmailCount
                    into #tt
                    from dbo.UserProfiles where isstaff = 1

                    select userid,firstname, lastname, ISNULL(EmailCount,0) As EmailCount from #tt

Any help would be appreciated.

Thanks, Chris

Solution 1

You need to move the isnull function outside the correlated subquery, like this:

SELECT  FirstName, 
        LastName,
        IsNull((
            SELECT  COUNT(UP1.EmailAddress) AS HasEmail 
            From    dbo.UserProfiles AS UP1 
            WHERE   (NOT (UP1.EmailAddress IS NULL)) 
                    AND (CreatedBy = dbo.UserProfiles.UserID)
            GROUP BY CreatedBy), 0) AS EmailEnteredCount 
FROM    dbo.UserProfiles 
WHERE   (IsStaff = 1)

Solution 2

It would make more sense if your isnull was inside your count. Try this:

count(isnull(up1.emailaddress, 0))

Comments

[Chris]

Thanks for the quick response. I tried that and it is still returning a null.

[rhealitycheck]

I thought that you were also filtering out the nulls using this "WHERE (NOT (UP1.EmailAddress IS NULL))" though i'm unclear why you aren't using "IS NOT NULL" and instead have the not outside the parentheses. I guess if it's possible can you give us some sample data like say 5 rows or something so it's possible to see what you are working with?

[Chris]

Added sample result data. The 'not' thing is because I have not worked with tsql in long time and that was just how it popped into my head.

[rhealitycheck]

ok, i see that. now that i'm looking more closely at the query -- why don't you join the two tables? that might make it simpler: select firstname, lastname, count(isnull(up1.emailaddress, 0)) as has email from dbo.userprofiles as up1 join dbo.uperprofiles as up2 on up1.createdby = up2.userid and up1.emailaddress is not null where up2.isstaff = 1 --please correct me if i've misunderstood why you are using a subquery

[Chris]

Thank you, now that I see it, it make perfect sense.

[Chris]

After reading you comment, I went back and did that as well and I think that solutions works as well and is probably better performance wise. Like I said before been a long time since I worked with sql and right now it is like learning to ride again. Thanks for your help and working with me.

[rhealitycheck]

no problem, typically joins are faster than subqueries so i try to avoid using subqueries when possible and advise others to do the same. it's just practice, the more you do it the more you remember and learn best practices.