The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Can you use function inside HAVING clause

Can you use function inside HAVING clause

Super Learner
0
Votes
1
Answer

Hi,
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.
select
year(tde.from_date) as calender_year,
te.gender,
count(te.gender) as number_of_employees
from
t_employees te
join
T_dept_emp tde on te.emp_no=tde.emp_no
group by te.gender, year(tde.from_date)
having year(tde.from_date)>=1990
order by year(from_date);

1 Answer

365 Team
0
Votes

Hi jo!
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.
https://365datascience.teachable.com/courses/360102/lectures/11680913
Hope this helps but please get back to us should you need further assistance. Thank you.
Best,
Martin

×
LAST CHANCE
Ready to Learn Data Science?
50% OFF