Query Regarding Having assignment in SQL.

Question

Hello team,
I was having doubt in one of the assignment questions of HAVING CLAUSE.

I was curious to know why output of the 2 statements differ:

SELECT
*, AVG(salary)
FROM salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000
ORDER BY Emp_no;

SELECT
*, AVG(salary)
FROM salaries
WHERE
salary > 120000
GROUP BY emp_no
ORDER BY emp_no;

I was thinking along the lines that the 2nd query also tracks all the individual records having salary greater than 120000 even the ones whose avg is < 120000, so is my logic correct or there is other reason behind the same.

Thanks for the intuitive course too, I am enjoying it fairly.
Kind Regards
Yuvraj.

Answer 1

Hi Yuvraj!

Thanks for reaching out.

This is a great question.
When we use WHERE above, only records containing salaries higher than 120000 have been used in the calculation of the average.
In the example involving HAVING, only averages higher than 120000 are obtained.

Hope this helps.
Best,
Martin

Answer 2

Hello Martin,
Thank you for your reply.
Yup it cleared my doubt.

Have a sweet weekend,
Kind Regards
Yuvraj.

Answer 3

Hi Yuvraj!

Thanks for your reply.

Great! Good luck while completing the course and please feel free to get back to us should you need further assistance.

Kind regards,
Martin

Query Regarding Having assignment in SQL.

Submit an answer

related questions