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

Follow this topic

MINHEE SUNG

Last answered:

09 Apr 2021

Posted on:

08 Apr 2021

Resolved: task 4: sql error code Unknown column

in SQL + Tableau / Problem 4: Solution in SQL

1 answers ( 1 marked as helpful)

Martin Ganchev

Instructor

Posted on:

09 Apr 2021

Helpful answer

Resolved: task 4: sql error code Unknown column

Submit an answer

related questions