Showing all values in Group By with inclusion of CASE

Question

I have the following data

CREATE TABLE #Test
(
    Severity    NVARCHAR(50)
    ,WorkId     INT
)

INSERT INTO #Test VALUES('High',1)
INSERT INTO #Test VALUES('Critical',2)

SELECT
    CASE 
        WHEN Severity IN ('High','Critical') THEN 'Critical'
        WHEN Severity IN ('Low','Medium') THEN 'Medium'
    END AS 'Severity'
    ,COUNT(*) AS 'Total'
FROM #Test 
GROUP BY 
    CASE 
        WHEN Severity IN ('High','Critical') THEN 'Critical'
        WHEN Severity IN ('Low','Medium') THEN 'Medium'
    END

This results in output -

Severity | Total
---------+-------
Critical |   2

I am expecting the following output -

Severity | Total
---------+-------
Critical |   2
 Medium  |   0

I have looked into the following two links which details a similar case but not same and am still unable to get the result -

http://ask.sqlservercentral.com/questions/47705/showing-null-values-as-well-in-group-by-in-sql.html

How to return empty groups in SQL GROUP BY clause

Any help or links?

Further update.

Having tried the solution below, the results are still not appearing. Pasting here the actual code wherein I would need to apply the logic

SELECT  s.NewSeverity AS 'Severity'
        ,COUNT(WI.microsoft_vsts_common_severity) AS 'Total'
FROM   ( VALUES
            ('Critical','I-High')
            ,('High','I-High')
            ,('Medium','I-Low')
            ,('Low','I-Low')
        )s(OldSeverity,NewSeverity)
       LEFT JOIN DimWorkItem WI (NOLOCK) 
            ON WI.microsoft_vsts_common_severity = s.OldSeverity
       JOIN dbo.DimPerson P 
         ON p.personsk = WI.system_assignedto__personsk 
       JOIN DimTeamProject TP 
         ON WI.TeamProjectSK = TP.ProjectNodeSK 
       JOIN DimIteration Itr (NOLOCK) 
         ON Itr.IterationSK = WI.IterationSK 
       JOIN DimArea Ar (NOLOCK) 
         ON Ar.AreaSK = WI.AreaSK 
WHERE  TP.ProjectNodeName = 'ABC' 
       AND WI.System_WorkItemType = 'Bug' 
       AND WI.Microsoft_VSTS_CMMI_RootCause <> 'Change Request' 
       AND Itr.IterationPath LIKE '%\ABC\R1234\Test\IT%' 
       AND WI.System_State NOT IN ( 'Rejected', 'Closed' ) 
       AND WI.System_RevisedDate = CONVERT(datetime, '9999', 126)            
GROUP BY s.NewSeverity

In actual there are only two 'Low' items, hence the output I am getting is I-Low, 2 whereas I want even I-High to appear with 0 count.

GarethD · Accepted Answer · 2014-03-06T17:49:00.737

The way I would go about this is to create your own table of values using a table value constructor:

SELECT  OldSeverity, NewSeverity
FROM    (VALUES 
            ('Critical', 'Critical'),
            ('High', 'Critical'),
            ('Medium', 'Medium'),
            ('Low', 'Medium')
        ) s (OldSeverity, NewSeverity);

This gives a table you can select from, then left join to your existing table:

SELECT  Severity = s.NewSeverity,
        Total = COUNT(t.Severity)
FROM    (VALUES 
            ('Critical', 'Critical'),
            ('High', 'Critical'),
            ('Medium', 'Medium'),
            ('Low', 'Medium')
        ) s (OldSeverity, NewSeverity)
        LEFT JOIN #Test t
            ON t.Severity = s.OldSeverity
GROUP BY s.NewSeverity;

This will give the desired results.

Example on SQL Fiddle

EDIT

The problem you have with the way that you are implimenting the query, is that although you have immediately left joined to DimWorkItem you then inner join to subsequent tables and refer to columns in WorkItem in the where clause, which undoes your left join and turns it back into an inner join. You need to place your whole logic into a subquery, and left join to this:

SELECT  s.NewSeverity AS 'Severity'
        ,COUNT(WI.microsoft_vsts_common_severity) AS 'Total'
FROM   ( VALUES
            ('Critical','I-High')
            ,('High','I-High')
            ,('Medium','I-Low')
            ,('Low','I-Low')
        )s(OldSeverity,NewSeverity)
       LEFT JOIN 
       (    SELECT  wi.Severity
            FROM    DimWorkItem WI (NOLOCK) 
                   JOIN dbo.DimPerson P 
                     ON p.personsk = WI.system_assignedto__personsk 
                   JOIN DimTeamProject TP 
                     ON WI.TeamProjectSK = TP.ProjectNodeSK 
                   JOIN DimIteration Itr (NOLOCK) 
                     ON Itr.IterationSK = WI.IterationSK 
                   JOIN DimArea Ar (NOLOCK) 
                     ON Ar.AreaSK = WI.AreaSK 
            WHERE  TP.ProjectNodeName = 'ABC' 
                   AND WI.System_WorkItemType = 'Bug' 
                   AND WI.Microsoft_VSTS_CMMI_RootCause <> 'Change Request' 
                   AND Itr.IterationPath LIKE '%\ABC\R1234\Test\IT%' 
                   AND WI.System_State NOT IN ( 'Rejected', 'Closed' ) 
                   AND WI.System_RevisedDate = CONVERT(datetime, '9999', 126)         
        ) WI
            ON WI.Severity = s.OldSeverity   
GROUP BY s.NewSeverity;

Spot on. Thanks a lot and it also helped me to understand solution better. — VKarthik, Mar 06 '14 at 18:09

Showing all values in Group By with inclusion of CASE

Further update.

1 Answers1