How to display 0 in all the columns in a table when sum returned for all the columns is 0?-CodePudding

Currently I am getting Blank columns But I want 0 Here as shown in the image. Click here for the Image. Below is my query and I want all the columns to display 0 when sum returned is 0.

SELECT COUNT(*) AS Inserted,
SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS Pending,
SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS Completed,
SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS Failure 
FROM [dbo].[ETIME_TIMECARD]
where cast(CreateDateTime as date) = CAST(GETDATE() AS DATE)
Group By cast(CreateDateTime as date)

CodePudding user response：

You're getting no results because your WHERE isn't returning any results and you have a GROUP BY with nothing to group on. In truth, the GROUP BY appears to not be required.

SELECT COUNT(*) AS Inserted,
       ISNULL(SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END),0) AS Pending,
       ISNULL(SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END),0) AS Completed,
       ISNULL(SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END),0) AS Failure 
FROM [dbo].[ETIME_TIMECARD]
WHERE CreateDateTime >= CAST(GETDATE() AS date)
  AND CreateDateTime < CAST(DATEADD(DAY, 1, GETDATE()) AS date);

Though, personally, I would switch to a COUNT as you clearly are counting, and then you don't need to ISNULL, as a COUNT will not return NULL:

SELECT COUNT(*) AS Inserted,
       COUNT(CASE status WHEN 0 THEN 1 END) AS Pending,
       COUNT(CASE status WHEN 1 THEN 1 END) AS Completed,
       COUNT(CASE status WHEN 2 THEN 1 END) AS Failure 
FROM [dbo].[ETIME_TIMECARD]
WHERE CreateDateTime >= CAST(GETDATE() AS date)
  AND CreateDateTime < CAST(DATEADD(DAY, 1, GETDATE()) AS date);