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]