Using MySQL CASE statement with temporary variable, and CASE result is incorrect?

Question

I have a sample company HR database, where the tables employees and jobs.

employees
job_id name salary

jobs
job_id job_title

And my goal is to output a table with each job title and the number of employees in that job title, and the average salary of each job so the table looks something like this:

Job Title  Number of Employees  Average Salary  Range

And this is my current SQL code:

SELECT j.job_title AS 'Job Title', 
       COUNT(*) AS 'Number of Employees', 
       @averageSalary := ROUND(AVG(e.salary), 0) AS 'Average Salary',
       CASE 
           WHEN @averageSalary BETWEEN 0 AND 5000 THEN 'Low'
           WHEN @averageSalary BETWEEN 5001 AND 10000 THEN 'Medium'
           WHEN @averageSalary > 10001 THEN 'High'
       ELSE 'N/A' END AS 'Range'
FROM employees AS e
JOIN jobs AS j ON e.job_id = j.job_id
GROUP BY e.job_id;

And I get an output which is:

Job Title Number of Employees Salary Range
'Public Accountant', '1', '8300', 'High'
'Accounting Manager', '1', '12000', 'Medium'
'Administration Assistant', '1', '4400', 'High'
'President', '1', '24000', 'Low'

This is obviously wrong because according to the CASE statement I have, The Public Accountant's range should be 'Medium'. And I'm not sure why this is wrong. Obviously, I don't get any error, so I've been just searching for CASE result wrong, but I cannot seem to find something that helps me to understand what is going on.

What am I doing wrong? Where in my syntax is generating different outputs? Thank you in advance!

Answer 1

Using variables like that is tricky, because the order of evaluation of expressions in the select clause is undefined. You cannot tell before hand if the variable will be assigned before of after the case expression is evaluated.

You can either repeat the expression, or use a subquery:

select j.job_title, e.*,
        case 
            when e.average_salary between 0 and 5000 then 'low'
            when e.average_salary between 5001 and 10000 then 'medium'
            when e.average_salary > 10001 then 'high'
            else 'n/a' 
        end as salary_range
from (
    select job_id, 
        count(*) as number_of_employees, 
        round(avg(salary), 0) as average_salary
    from employees
    group by job_id
) e
inner join jobs j on j.job_id = e.job_id