Skip to content
Advertisement

CASE WHEN with GROUP BY

I have the following query :

SELECT
    [Year]
   ,[Month]
   ,CASE
        WHEN Dept IN ('Fin','Sales') THEN 'Temp EU'
        WHEN (Dept  = 'HR' AND
            [Status] LIKE '%IN%') THEN 'Temp MEA'
        ELSE Dept 
    END AS Dept 
   ,COUNT(DISTINCT EmpID) AS CountEmp
FROM Employees AS E WITH (NOLOCK)
GROUP BY [Year]
   ,[Month]
   ,Dept 

I get this following error :

Column ‘Status’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Advertisement

Answer

SELECT 
[Year]
,[Month]
,SUM(CountEmp)
FROM (
SELECT
    [Year]
   ,[Month]
   ,CASE
        WHEN Dept IN ('Fin','Sales') THEN 'Temp EU'
        WHEN (Dept  = 'HR' AND
            [Status] LIKE '%IN%') THEN 'Temp MEA'
        ELSE Dept 
    END AS Dept 
   ,COUNT(DISTINCT EmpID) AS CountEmp
FROM Employees AS E 
GROUP BY [Year]
   ,[Month]
   ,Dept
  ,[Status] ) AS Q
GROUP BY 
 [Year]
,[Month]
,[Dept]
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement