Resolved: task 4: sql error code Unknown column
Hi, I'd like to ask a question about this SQL query.
-----------------
DROP PROCEDURE IF EXISTS filter_salary;
DELIMITER $$
CREATE PROCEDURE filter_salary (IN p_min_salary FLOAT, IN p_max_salary FLOAT)
BEGIN
SELECT
e.gender, d.dept_name, AVG(s.salary) as avg_salary
FROM
t_salaries s
JOIN
t_employees e ON s.emp_no = e.emp_no
JOIN
t_dept_emp de ON de.emp_no = e.emp_no
JOIN
t_departments d ON d.dept_no = de.dept_no
WHERE s.salary BETWEEN p_min_salary AND p_max_salary
GROUP BY d.dept_no, e.gender;
END$$
DELIMITER ;
CALL filter_salary(50000, 90000);
--------------------
When I changed s.salary in the where clauseto avg_salary and called the procedure, I got an error code of [Unknown column 'avg_salary' in 'where clause'].
How come I can't use avg_salary instead of s.salary?
I thought avg_salary was better-suited because we are interested in the average salary which is between min and max salary.
Thanks in advance!
Hi Minhee!
Thanks for reaching out.
This is a brilliant question. I understand where this confusion is coming from, since I was quite perplexed about the same a while ago when I was starting.
I'd say the trick is to try to think like the SQL Optimizer, not like a human, so to speak.
Reading the query from top to bottom makes us think that what we've written in the lines above should already be understood by the software while executing the lines of code below.
That's not how it works, though.
The software tool (in this case - the SQL Optimizer, which is the tool that translates our MySQL queries into a machine language, so to speak) reads the entire query and then sets separate tasks to the computer. Therefore, the computer can follow its own logic and order while executing the task.
That's exactly what happens here. While running the requested operation, the Optimizer will first refer to what we have in the FROM clause (which will navigate it through the right table from our database), and then it will take into account what we've specified in the WHERE clause (which is where we designate which records we'd like to be working with). Please note that the latter happens before any commands we've specified in the SELECT statement (or, e.g., in a GROUP BY clause, if we had one).
That's why, we need to use s.salary in the WHERE clause. This will help "the SQL Optimizer understand us". "Then", we can help ourselves with aliases in the field list, i.e. right after the SELECT keyword (although the syntax requires from us to write that before/above the WHERE clause). Consequently, these aliases can be used in the GROUP BY clause, for instance.
Hope this helps.
Best,
Martin
Additionally, If you still want to use average salary for the parameters, put the query you have written in subquery to make the table and then use where clause to get average salary range.
Hi Keyur!
Thanks for reaching out and sharing your solution with the Community!
Kind regards,
Martin