sqlsql-serversql-server-2012-express

SQL issue with CASE statement


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,


Solution

  • 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