Averaging is rounding but don't want it to 1

[DebbieC]

I am trying to get an average of some fields and instead of 48.85 it's returning 48.

How can I fix this?

 

[tc3596]

if you are dumping the value into a table, make sure the table isn't an 'int'. Other than that, you'll have to be more specific. Give me details.

 

[ESquared]

Avg(convert(float, ColumnName))

 

[SQLBill]

An average of integers is an integer unless you convert them to another numeric datatype that allows decimals. ESquared showed one way. Converting to DECIMAL(4,2) should work also.

-SQLBill

Posting advice: FAQ481-4875

 

[DebbieC]

Thanks ESquared. Your suggestion worked. I had tried something similar to that with the float but the syntax must have been wrong because it didn't work.

I had already tried converting it to a decimal but I got an error message that said:

Arithmetic overflow error converting numeric to data type numeric.

Thanks for everyone's help.

 

[SQLBill]

With the error, you probably didn't make your DECIMAL precision and scale large enough.

For the future, remember the first number is ALL the digits, and the second number is the amount of digits to the right of the decimal point.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

Precision can be very misleading with decima. Float is probably a better way to go to maintain the best final precision, even if converting to decimal at the end.