Last answered:

09 Apr 2021

Posted on:

08 Apr 2021

0

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!

1 answers ( 1 marked as helpful)
Instructor
Posted on:

09 Apr 2021

1

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

Submit an answer