I'm trying to adjust the following statement:
SELECT CASE WHEN OrganizationLevel < 2 THEN UPPER(JobTitle)
ELSE JobTitle END as 'Job Title', COUNT(BusinessEntityID) as 'number of employees'
FROM HumanResources.Employee
WHERE OrganizationLevel < 3
GROUP BY JobTitle, OrganizationLevel
ORDER BY JobTitle ASC
I need to change it so as to make JobTitle appear as 'SOMETHING ELSE' when the OrganisationLevel is 1.
I thought this would be a simple matter of making a small change to the CASE statement so JobTitle would be = 'SOMETHING ELSE' but it won't allow me to do this,
You need to have same case
statement in Group by
SELECT CASE
WHEN OrganizationLevel = 1 THEN 'SOMETHING_ELSE'
WHEN OrganizationLevel < 2 THEN Upper(JobTitle)
ELSE JobTitle
END AS 'Job Title',
Count(BusinessEntityID) AS 'number of employees'
FROM HumanResources.Employee
WHERE OrganizationLevel < 3
GROUP BY JobTitle,
CASE
WHEN OrganizationLevel = 1 THEN 'SOMETHING_ELSE'
WHEN OrganizationLevel < 2 THEN Upper(JobTitle)
ELSE JobTitle
END
ORDER BY JobTitle ASC