By greeshma y


2019-01-11 10:36:20 8 Comments

I'm solving a hacker rank challenges and i'm stuck with an error

Msg 8117, Level 16, State 1, Server WIN-ILO9GLLB9J0, Line 2
Operand data type varchar is invalid for avg operator.

kindly suggest the chnages

I have tried writing this query

SELECT CEILING((AVG(Salary))-(AVG(REPLACE(Salary,0,'')))) FROM EMPLOYEES

2061

4 comments

@Salman A 2019-01-11 11:53:18

I guess this has something to do with a broken "0" key. Anyway, REPLACE returns a string which cannot be used inside AVG. You need to cast it back to an integer:

SELECT AVG(Salary) - AVG(CAST(REPLACE(Salary, 0, '') AS INT))
FROM Employees

Replace INT with the same data type as that of Salary column.

SQL Server specific: if Salary is an integer then CEILING is not required since AVG will return a truncated integer. You must cast as decimal if you want to include decimal part in results.

@greeshma y 2019-01-12 15:32:13

It worked after casting ....

@Larnu 2019-01-11 10:49:38

I think the "true" answer here is to fix your data type. You can so by using the ALTER syntax:

ALTER TABLE dbo.EMPLOYEES ALTER COLUMN Salary decimal(18,2); --Assumed dbo schema

You may want to use a different datatype to decimal(18,2), but that gives you the basic syntax.

Then a simple expression like AVG(Salary) won't generate an error about Salary being the wrong datatype for the operator.

@Salman A 2019-01-11 11:26:54

We don't know if the column is varchar. SELECT REPLACE(1234.56, 0, '') works as-is on the decimal(6,2).

@Suraj Kumar 2019-01-11 10:47:06

You can try this

SELECT CEILING((AVG(CAST('2061' as Int)))-(AVG(CAST('2061' as Int)))) FROM EMPLOYEES

@SQL_M 2019-01-11 10:38:12

Your Salary column is varchar, should be a numeric type.

SELECT CEILING((AVG(CAST (Salary AS DECIMAL (10,2))))-(AVG(CAST (Salary AS DECIMAL (10,2))))) FROM EMPLOYEES

@SQL_M 2019-01-11 10:41:51

Thanks @HoneyBadger, copy paste issue. ;)

@Salman A 2019-01-11 10:43:52

It is not necessarily varchar.

Related Questions

Sponsored Content

9 Answered Questions

[SOLVED] How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

4 Answered Questions

[SOLVED] Trying to sum two columns in SQL Server results in error message

  • 2017-08-09 20:27:06
  • WWaldo
  • 248 View
  • 3 Score
  • 4 Answer
  • Tags:   sql sql-server sum

1 Answered Questions

[SOLVED] T-SQL Challenges from hackerrank

  • 2017-03-14 01:30:34
  • user5087404
  • 472 View
  • 0 Score
  • 1 Answer
  • Tags:   sql-server

1 Answered Questions

1 Answered Questions

[SOLVED] Operand data type varchar is invalid for subtract operator. in SQL Server

  • 2016-10-20 08:35:01
  • user3262364
  • 2211 View
  • 0 Score
  • 1 Answer
  • Tags:   sql sql-server

1 Answered Questions

2 Answered Questions

[SOLVED] Operand data type nchar is invalid for avg operator

4 Answered Questions

[SOLVED] SQL Server 2008 - calculation of avg. timing

3 Answered Questions

[SOLVED] Exec Stored Procedure - Invalid Operator

Sponsored Content