Operand data type nchar is invalid for avg operator

I have a table that saves the size of file in the database when the user uploads it. I want to get the average value of all the size that the user uploaded.

I have the following column as example that shows the size in Mb's

|Size| |1.20| |0.25| |0.50| 

The result that I want as average is something like this

|Size| |0.65| 

When I'm trying to get the average I get this error

Msg 8117, Level 16, State 1, Line 15 Operand data type nchar is invalid for avg operator.

EDIT I have changed the column type to nvchar and get this error message when I'm converting it to int

Conversion failed when converting the nvarchar value '0,24' to data type int.

When I tried it with a decimal I get this error message

Msg 8114, Level 16, State 5, Line 11 Error converting data type nvarchar to numeric.

What can I do to fix this problem.

2 Answers

The error shows you have 0,24 as a number in Swiss or German format.

This is different to 0.24 in British or US format.

So if the datatype was correct as decimal or float, 0,24 would not be allowed because SQL Server does not really deal with continental number formats. See SQL server with german regional settings for more

Then, neither is integer either of course so the int conversion fails.

So, fix the column datatype and the data to fix the error. And you can also avoid nasty client number to string conversions like 24E-2 which is only recognised as float.

And what if you have thousand separators too with mixed format? Imagine this dataset

123.456,79 234,567.89 34E5 0,24 0.24 2.3E-1 

Fixing the data will require some LIKE searches. At least these to fix each format one by one.

 LIKE '%,%.%' LIKE '%.%,%' LIKE '%,%' LIKE '%E%' 

0.24 won't convert to an int as it has decimal part.

You need to do CAST([size] as DECIMAL(9,2)) or some such...

Although we could really do with seeing your code :)

To use this CAST as part of an aggregate...

SELECT [database], AVG(CAST([size] as DECIMAL(9,2))) AS [Average of Size] FROM table GROUP BY [database] 

Of course I don't know what your table or query actually is...

As others have said - if you are are going to be taking the average anyway then you will be better off not converting the number to an NVARCHAR or VARCHAR in the first place and working with the plain numeric fields.

As gbn notes this is 0,24 continental format so...

SELECT [database], AVG(CAST(REPLACE([size],',','.') as DECIMAL(9,2))) AS [Average of Size] FROM table GROUP BY [database]