I have the following query :
x
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]