CODEWITHSUNDEEP
sqlsql-servert-sql
Martin James
Martin James

Reputation: 146

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.

Upvotes: 0

Views: 83

Answers (3)

Nicholas Hunter
Nicholas Hunter

Reputation: 1845

I am a huge fan of common table expressions (AKA subqueries) to clarify what is going on in a complicated query. This looks much cleaner to me.

With Qry1 As (
    SELECT      [EmpId],
                [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
    FROM        Employees
)
SELECT      [Year], 
            [Month],
            Dept,
            COUNT(DISTINCT EmpID) AS CountEmp 
FROM        Qry1
GROUP BY    [Year],
            [Month],
            Dept

Upvotes: 1

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

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]

Upvotes: 0

Sergey
Sergey

Reputation: 5250

 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]
  CASE
    WHEN Dept IN ('Fin','Sales') THEN 'Temp EU'
    WHEN (Dept  = 'HR' AND
        [Status] LIKE '%IN%') THEN 'Temp MEA'
    ELSE Dept 
  END

Upvotes: 3

Related Questions