Jeff Chambers Jeff Chambers - 4 months ago 22
SQL Question

SQL Server 2012 AVG statement

I am having an issue trying to use a certain statement and I did EXACTLY what I saw on

youtube
and I still get it incorrect. Here is my issue

SELECT AVG(Salary) AS Average_Salary FROM Employee;

Error: Operand data type varchar is invalid for avg operator.


I did the exact same thing and still got it wrong, but in the
youtube video
it was correct.

What could I be doing wrong?

Answer
  • Your data is not clean or it is formatted incorrectly.

    SELECT SALARY FROM dbo.Employee WHERE ISNUMERIC(Salary) = 1

likely you will find your salaries include the ( , ) in your numbers, which cannot be converted into a numeric integer by implicit conversions.

SQL Server is smart, but it must follow precedence of conversion. Trying to implicitly convert a non-numeric varchar into an integer is often considered an illegal conversion. AVG takes an explicit numeric value, so trying to implicitly convert a string would cause the function to lose information (which it cannot do implicitly).

Instead, convert the value into a data type that can be flexible with non-numeric values such as

SELECT AVG(CAST(Salary AS MONEY)) as SALARY_AVG
FROM Employees

For a better understanding of implicit conversions and the limitations, see DATA TYPE CONVERSIONS