28 Jun 2021

Posted on:

25 Jun 2021

0

# Query Regarding Having assignment in SQL.

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.

Instructor
Posted on:

26 Jun 2021

0

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

Posted on:

27 Jun 2021

0

Hello Martin,
Yup it cleared my doubt.

Have a sweet weekend,
Kind Regards
Yuvraj.

Instructor
Posted on:

28 Jun 2021

0

Hi Yuvraj!