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