when replacing the (MAX(s.salary) - MIN(s.salary) by its aliase it will not work .. why?

Mohammad Al Mashwakhi 0
Viewed 36
SELECT dm.emp_no, 
       e.first_name, 
       e.last_name,
       MAX(s.salary) - MIN(s.salary) AS salary_difference,
       CASE WHEN MAX(s.salary) - MIN(s.salary) > 30000 
            THEN 'Salary was raised by more then $30,000'
            ELSE 'Salary was NOT raised by more then $30,000'
        END AS salary_raise
    FROM dept_manager dm
    JOIN employees e ON e.emp_no = dm.emp_no
    JOIN salaries s ON s.emp_no = dm.emp_no
    GROUP BY s.emp_no;

when replacing the (MAX(s.salary) - MIN(s.salary) by its aliase it will not work .. why ?

mysql
1 Answers

You should read Problems with Column Aliases

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.

In your case alias would work if an outer query is involved, which by the way would be excess.

Something like this:

SELECT  emp_no,
        first_name,
        last_name,
        salary_difference,
        CASE WHEN salary_difference > 30000 
             THEN 'Salary was raised by more then $30,000'
             ELSE 'Salary was NOT raised by more then $30,000'
         END AS salary_raise
FROM (
      SELECT dm.emp_no, 
             e.first_name, 
             e.last_name,
             MAX(s.salary) - MIN(s.salary) AS salary_difference
       FROM dept_manager dm
       JOIN employees e ON e.emp_no = dm.emp_no
       JOIN salaries s ON s.emp_no = dm.emp_no
       GROUP BY s.emp_no
      ) as tbl
Ergest Basha0
Related