sqlcase-statement

How can I use case information in a new column in SQL?


I am learning SQL from an online tutorial series and in this video we learned how to use case statements.

I understand how to use it, but then I thought about "well can I use the new data from the column I made?" I tried playing around and added in another CASE statement to try and use the UpdatedSalary column, but it wouldn't work. I tried googling a bunch, but I really don't know how to word the question.

SELECT 
    FirstName, LastName, JobTitle, Salary,
    CASE 
        WHEN JobTitle = 'Salesman' THEN Salary + (Salary * .10)
        WHEN JobTitle = 'Accountant' THEN Salary + (Salary * .05)
        WHEN Jobtitle = 'HR' THEN Salary + (Salary * .00001)
        ELSE Salary + (Salary * .03)
    END AS UpdatedSalary
    CASE 
        WHEN UpdatedSalary > 0 THEN UpdatedSalary - Salary 
        ELSE 
    END AS NetIncrease
FROM 
    SQLTutorial.dbo.EmployeeDemographics
JOIN 
    SQLTutorial.dbo.EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

Solution

  • You can not use column alias like this if you want to use this you need to put same condition in both the columns or need to use outer query.

    SELECT FirstName, LastName, JobTitle, Salary,
    CASE 
        WHEN JobTitle = 'Salesman'
        THEN Salary + (Salary * .10)
        WHEN JobTitle = 'Accountant'
        THEN Salary + (Salary * .05)
        WHEN Jobtitle = 'HR' 
        THEN Salary + (Salary * .00001)
        ELSE Salary + (Salary * .03)
    END
    AS UpdatedSalary,
    
    CASE 
        WHEN (CASE 
        WHEN JobTitle = 'Salesman'
        THEN Salary + (Salary * .10)
        WHEN JobTitle = 'Accountant'
        THEN Salary + (Salary * .05)
        WHEN Jobtitle = 'HR' 
        THEN Salary + (Salary * .00001)
        ELSE Salary + (Salary * .03)
    END)> 0
        THEN (CASE 
        WHEN JobTitle = 'Salesman'
        THEN Salary + (Salary * .10)
        WHEN JobTitle = 'Accountant'
        THEN Salary + (Salary * .05)
        WHEN Jobtitle = 'HR' 
        THEN Salary + (Salary * .00001)
        ELSE Salary + (Salary * .03)
    END)- Salary 
        ELSE
    END
    AS NetIncrease
    
    FROM SQLTutorial.dbo.EmployeeDemographics
    JOIN SQLTutorial.dbo.EmployeeSalary
        ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID
    

    Or

    SELECT FirstName, LastName, JobTitle, Salary,UpdatedSalary,
    CASE 
        WHEN UpdatedSalary > 0
        THEN UpdatedSalary - Salary 
        ELSE
    END
    AS NetIncrease
    
    from (
    SELECT FirstName, LastName, JobTitle, Salary,
    CASE 
        WHEN JobTitle = 'Salesman'
        THEN Salary + (Salary * .10)
        WHEN JobTitle = 'Accountant'
        THEN Salary + (Salary * .05)
        WHEN Jobtitle = 'HR' 
        THEN Salary + (Salary * .00001)
        ELSE Salary + (Salary * .03)
    END
    AS UpdatedSalary
    
    
    FROM SQLTutorial.dbo.EmployeeDemographics
    JOIN SQLTutorial.dbo.EmployeeSalary
        ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID)t