SQL - put null values in 0

Question

SQL - put null values in 0

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have a pivot table that returns me some null values in SUM(1) . I wanted them to be zero rather than null, but I can not.

Original pivot table

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 is what I did with the case when trying 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;

It still gives me nulls, I have already tried with the coalesce and it does the same. I can not understand what I can do wrong. Thank you

sql null table pivot

asked by anonymous 02.06.2015 / 13:31

2 answers

Object Orientation in Java [closed] Linux runtime

score 1 · Answer 1

You have a 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 ISNull function.

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

link

Then use Coalesce in your Sum field and see if it does what you want.

score 1 · Answer 2

Does anything prevent the use of COUNT(*) instead of SUM(1) ? COUNT() , unlike SUM() , returns zero when the set on which the function is working is empty (and especially in this case, it makes it much clearer to who is reading the code what is being done.)