Skip to content
Advertisement

How to Replace NULL Value with 0 (Zero)?

I’ve just got myself stuck with some SQL query and I’m quite new on this. I’m using pivot in my query.

This is my SELECT query:

SELECT * 
FROM 
    (SELECT lg.domainNameID AS [Domain ID], COUNT(lg.domainNameID) AS [Fix Count]
     FROM tbl_ATT_Request r
     INNER JOIN tbl_ATT_Login lg ON lg.workdayID = r.workdayID
     WHERE r.requestCategoryID = 1 
     GROUP BY lg.domainNameID) slct

and this is the output:

Domain | Fix Count
-------+-----------
1        1
2        1
4        2
5        1

And this is my query with PIVOT.

SELECT * 
FROM 
    (SELECT lg.domainNameID AS [Domain ID], COUNT(lg.domainNameID) AS [Fix Count]
     FROM tbl_ATT_Request r
     INNER JOIN tbl_ATT_Login lg ON lg.workdayID = r.workdayID
     WHERE r.requestCategoryID = 1 
     GROUP BY lg.domainNameID) slct
PIVOT
    (SUM(slct.[Fix Count])
         FOR slct.[Domain ID] IN ([1],[2],[3],[4],[5])
    ) AS pvt

This is the output:

1 | 2 | 3    | 4 | 5
1   1   NULL   2   1

Now my problem is how can I replace the NULL values with 0.

Advertisement

Answer

Just use conditional aggregation:

SELECT SUM(CASE WHEN Domain_Id = 1 THEN Fix_Count ELSE 0 END) as d_1,
       SUM(CASE WHEN Domain_Id = 2 THEN Fix_Count ELSE 0 END) as d_2,
       SUM(CASE WHEN Domain_Id = 3 THEN Fix_Count ELSE 0 END) as d_3,
       SUM(CASE WHEN Domain_Id = 4 THEN Fix_Count ELSE 0 END) as d_4,
       SUM(CASE WHEN Domain_Id = 5 THEN Fix_Count ELSE 0 END) as d_5       
FROM (SELECT lg.domainNameID AS Domain_ID, COUNT(*) AS Fix_Count
      FROM tbl_ATT_Request r JOIN
           tbl_ATT_Login lg
           ON lg.workdayID = r.workdayID
      WHERE r.requestCategoryID = 1
      GROUP BY lg.domainNameID
     ) d
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement