I have below data

CREATE TABLE #EmployeeData
(
     EmpID INT,
     Designation VARCHAR(100),
     Grade CHAR(1)
)

INSERT INTO #EmployeeData (EmpID, Designation, Grade)
VALUES (1, 'TeamLead', 'A'),
       (2, 'Manager', 'B'),
       (3, 'TeamLead', 'B'),
       (4, 'SeniorTeamLead', 'A'),
       (5, 'TeamLead', 'C'),
       (6, 'Manager', 'C'),
       (7, 'TeamLead', 'D'),
       (8, 'SeniorTeamLead', 'B')

SELECT Designation,CASE WHEN COUNT(DISTINCT GRADE)>1 THEN 'MultiGrade' ELSE Grade END FROM 
#EmployeeData
GROUP BY Designation

Desired result:

 Designation      Grade
 --------------------------
 Manager        MultiGrade
 TeamLead       MultiGrade
 SeniorTeamLead      A

Note:

1. If designation has more than one grade then it is multigrade
2. If single grade is there then the particular grade
3. In case there is a combination with A and B then it should be A only

I tried with a query using case but I get this error:

Column '#EmployeeData.Grade' is invalid in the select list because it is not contained in either` an aggregate function or the GROUP BY clause.

Can anyone suggest the query to fetch the desired result?

CodePudding user response：

As the error says, you need to aggregate the columns you are not grouping by. So use MAX and MIN (as Jeroen commented).

SELECT Designation
  , CASE WHEN MAX(Grade) = 'B' AND MIN(Grade) = 'A' THEN 'A' WHEN MAX(Grade) <> MIN(Grade) THEN 'MultiGrade' ELSE MIN(Grade) END Grade
FROM #EmployeeData
GROUP BY Designation
ORDER BY Designation;

Your real world situation might be more complex, but the same principle applies.