T-SQL Group By with CASE statement

This article is about the usage of CASE statement within Group By clause .

For example : Consider below sample data

DECLARE @Tmp TABLE (Products VARCHAR(50),Count INT)
INSERT @Tmp SELECT 'SQL Server 2008',100
INSERT @Tmp SELECT 'SQL Server 2008',100
INSERT @Tmp SELECT 'SQL Server 2012',200
INSERT @Tmp SELECT 'SQL Server 2012',200
INSERT @Tmp SELECT 'ORACLE',600

Consider if we are required to calculate the overall count of the products like "SQL" and "Oracle" ,
then below query containing Group By clause with CASE statement can be used :

SELECT
CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
              WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
              ELSE NULL END Products ,
SUM(Count) OverallCount
FROM @Tmp
GROUP BY CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
              WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
              ELSE NULL END