CODEWITHSUNDEEP
sqlnullpivot
Mara Pimentel
Mara Pimentel

Reputation: 317

SQL - Change NULL value to 0

I have this pivot table that gives me null value on SUM(contar) and I want to change them to 0

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

I tried make this way but it gives me Null values again

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 don't understand what is wrong. Thank you

Upvotes: 0

Views: 133

Answers (1)

dnoeth
dnoeth

Reputation: 60513

You define aliases within the CASE, which is not allowed. Additionally you mix numeric and string:

It's either 

CASE WHEN SUM(1) IS NULL THEN 0 ELSE SUM(1) END AS contador

or

COALESCE(SUM(1), 0) END AS contador

Btw, SUM(1) is the same as COUNT(*)

Upvotes: 2

Related Questions