How to SET Default value in SQL Server case statement?

Question

I have a scenario like to display Status and Count. Look at the below model

Status            Counts
---------------   --------
Completed          10
In Progress         6
Others              0

I have this code:

SELECT 
    CASE 
       WHEN Status = 'I' THEN 'IN PROGRESS' 
       WHEN Status = 'O' THEN 'Others' 
       WHEN Status = 'C' THEN 'COMPLETED' 
    END AS ' Status', 
    COUNT(Status) AS ' Counts' 
FROM   
    table1

I have values for only Completed and InProgress. Currently I am getting only two rows. But I need three rows including Others and it's count should be 0 - how to do it in SQL?

Answer 1

Add an "else", like this:

SELECT CASE 
    WHEN Status= 'I' THEN 'IN PROGRESS' 
    WHEN Status= 'C' THEN 'COMPLETED' 
    ELSE 'Others' 
END AS ' Status'
FROM table1

Answer 2

From my understanding you have a main table with status values in it, which I'll simplify to this:

CREATE TABLE #MainTable
(
    id INT,
    [status] NVARCHAR(1)
)

Your problem is that if there are no rows with the status O for Others, you're not getting a 0 when you group the rows to get counts.

I suggest you create a Status table to link to with a RIGHT JOIN. This will also get rid of the need for your CASE statements.

Full solution:

CREATE TABLE #MainTable
    (
      id INT ,
      [status] NVARCHAR(1)
    );

INSERT  INTO #MainTable
        ( id, [status] )
VALUES  ( 1, 'I' ),
        ( 2, 'I' ),
        ( 3, 'I' ),
        ( 4, 'I' ),
        ( 5, 'C' ),
        ( 6, 'C' );

CREATE TABLE #status
    (
      [status] NVARCHAR(1) ,
      [statusText] NVARCHAR(15)
    );

INSERT  INTO #status
        ( status, statusText )
VALUES  ( 'I', 'In Progress' ),
        ( 'C', 'Completed' ),
        ( 'O', 'Others' );

SELECT  s.statusText ,
        COUNT(t.[status]) StatusCount
FROM    #MainTable t
        RIGHT JOIN #status s ON s.status = t.status
GROUP BY s.statusText;

DROP TABLE #MainTable;
DROP TABLE #status;

Produces:

statusText  StatusCount
Completed   2
In Progress 4
Others      0

In the above example it joins on the I,O,C string values, which I would suggest that you replace with ID values. Then you could do this:

CREATE TABLE #MainTable
    (
      id INT ,
      statusId INT
    );

CREATE TABLE #status
    (
      statusId INT ,
      statusShort NVARCHAR(1) ,
      statusText NVARCHAR(15)
    );

SELECT t.id ,
       t.statusId ,
       s.statusId ,
       s.statusShort ,
       s.statusText 
FROM #MainTable t
RIGHT JOIN #status s ON s.statusId = t.statusId

DROP TABLE #MainTable;
DROP TABLE #status;