For the following query, year() is set as calendar_year and can thus use calendar_year for the codes afterwards. I am curious why function like year() can not be used in HAVING clause. when I did it, there is an error. Thank you.
year(tde.from_date) as calender_year,
count(te.gender) as number_of_employees
T_dept_emp tde on te.emp_no=tde.emp_no
group by te.gender, year(tde.from_date)
order by year(from_date);
Thanks for reaching out and please excuse me for not getting back to you sooner.
This is to reflect the way in which the SQL syntax has been designed. It is indeed the use of alias in the having clause that allows us to apply conditions in the HAVING clause.
You can refer to the following lecture for more information about the only_full_group_by mode and see if reverting to the previous, or old, settings, helps you execute your code.
Hope this helps but please get back to us should you need further assistance. Thank you.