SELECT AVG(SALARY) FROM EMP yields inaccurate results - why?

[Anjali Chopra]

Hi,

In a interview I'm asked - My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results Why? I'm not sure about the answer, can someone please help?

[Anuj Dhawan]

AVG is an scalar function which would sum values of all the rows in the given column and divide the result by the number of rows - in other words, it'll provide you with the average for the particular column, on which you apply it. In this particular question the column is SALARY.

Having that in mind, if the column SALARY is not declared to have NULLs - it can give problems. Because in case, for the employees for whom the salary is not known, AVG will take them in account to give you the resultant average, which is not correct. For example, if an employee has not yet joined in and the NULL is not allowed, you've fill in "some default salary" for him/her; this "some default salary" is culprit in getting the average and you get inaccurate result.

Hope this helps.

[Anjali Chopra]

Thanks Anuj!

That's a great and simple worded explanation, Thanks for your help.