SQL - set null values to 0

Question

I have a table pivot that returns some null values in SUM(1). I wish they were zero instead of zero, but I’m not getting it.

Pivot table original

SELECT
        Description AS Categoria,
        [ACS],
        [URO] 
FROM 
        (
            SELECT 
                    GroupType.Description,
                    Speciality.Shortname,
                    SUM(1) as contar
            FROM
                    DoctorEnterpriseDetails 
                    INNER JOIN Speciality ON DoctorEnterpriseDetails.Speciality1 = Speciality.SpecialityId
                    INNER JOIN GroupType ON DoctorEnterpriseDetails.GroupId = GroupType.GroupId
            WHERE 
                    (DoctorEnterpriseDetails.EnterpriseId = 48)
            Group By 
                    GroupType.Description,
                    Speciality.Shortname
        ) as ps PIVOT (SUM(contar) FOR Shortname IN ([ACS],[URO])) pvt
ORDER BY 
    description

and this was the one I did with the case when to try to put the nulls to zero

SELECT 
        Description AS Categoria,
        [ACS],
        [URO]
FROM
        (
          SELECT 
                  GroupType.Description,
                  Speciality.Shortname,
                  GroupType.GroupId,
                  (CASE WHEN (SUM(1) IS NULL) THEN 0 ELSE SUM(1) END) AS contar
          FROM 
                  DoctorEnterpriseDetails
                  INNER JOIN Speciality ON DoctorEnterpriseDetails.Speciality1 = Speciality.SpecialityId
                  INNER JOIN GroupType ON DoctorEnterpriseDetails.GroupId = GroupType.GroupId
          WHERE 
                  (DoctorEnterpriseDetails.EnterpriseId = 48)
          GROUP BY 
                  GroupType.Description,
                  Speciality.Shortname,
                  DoctorEnterpriseDetails.GroupId,
                  GroupType.GroupId
        ) 
        AS ps PIVOT (SUM(contar) FOR Shortname IN ([ACS],[URO])) pvt
ORDER BY 
        GroupId;

I have tried with the coalesce and it does the same. I can’t figure out what’s wrong with it. Thank you

Answer 1

Something prevents the use of COUNT(*) in place of SUM(1)? COUNT(), unlike SUM(), returns zero when the set over which the function is operating is empty (and especially in this case, makes it much clearer to whoever is reading the code what is being done).

Answer 2

It has an SQL function called Coalesce. When a value is null it returns the value of the parameter you determine in it.

Or you can use the function Isnull.

Select Coalesce(Valor, 0) As Valor From Tabela
Select ISNull(Sum(Valor),0) As Valor From Tabela

https://msdn.microsoft.com/pt-br/library/ms190349.aspx

Then use the Coalesce in your Soma field and see if you do what you want.