In the HAVING lessons, it was mentioned that 'WHERE cannot use aggregate functions within its condition'. However, in the Practice Exam 3, Question 3, we used DATEDIFF() in the WHERE clause instead of the HAVING clause. When I used DATEDIFF() as per below query, there was an Error Code: 1054. Unknown column 'to_date' in 'having clause'. SELECT COUNT(emp_no) FROM salaries WHERE salary >= 104038 GROUP BY emp_no HAVING DATEDIFF(to_date, from_date) > 365;

Hi Yi! Thanks for reaching out. The reason is that when MySQL evaluates the HAVING clause, the values of columns specified in the clause have not been evaluated yet. Query order of execution FROM and JOINs. The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY LIMIT / OFFSET. Hope this helps. Best, Tsvetelin

Follow this topic

Yi Hao Cheong

Last answered:

25 Jun 2022

Posted on:

19 Jun 2022

Using DATEDIFF function in WHERE clause

in SQL / Welcome to SQL

1 answers ( 0 marked as helpful)

Tsvetelin Tsonkov

Posted on:

25 Jun 2022

Using DATEDIFF function in WHERE clause

Submit an answer

related questions