SQL issue with CASE statement

Question

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 get an error about group by? You should have the same statement in group by as you have in select — James Z, Mar 10 '15 at 15:51
group by doesn't give me an error, as is the statement works but i need it to show JobTitle as 'something else' when the organisationlevel = 1 — Xid Stargazer, Mar 10 '15 at 15:53
Oh then maybe the problem is that you have < 2 first so it uses that instead of = 1? — James Z, Mar 10 '15 at 15:54
Please post the query that didn't work, and what error you received if any. — Tab Alleman, Mar 10 '15 at 15:57

score 1 · Answer 1 · answered Mar 10 '15 at 15:52

1

   SELECT CASE WHEN OrganizationLevel < 2 THEN UPPER(JobTitle)
                    ELSE WHEN  OrganizationLevel =1 THEN 'SOMETHING ELSE' 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

answered Mar 10 '15 at 15:52

SouravA

5,147
2
24
49

'Something Else' will never be returned, because 1 < 2. – Tab Alleman Mar 10 '15 at 15:53
I could remove < 2 as it won't be necessary if I'm right but that doesn't solve the problem – Xid Stargazer Mar 10 '15 at 15:56
Correct. But it was just to illustrate how to fit an extra condition in CASE. – SouravA Mar 10 '15 at 15:58

score 1 · Accepted Answer · answered Mar 10 '15 at 15:53

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

close but It brings up an error : Column 'HumanResources.Employee.OrganizationLevel' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — Xid Stargazer, Mar 10 '15 at 15:59

SQL issue with CASE statement

2 Answers2