AVG function on an integer column- truncation

When I use the AVG Function on an integer column, the result is truncated

Example:

Select AVG(field1) from table1

Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.

I can get the correct result by using the following SELECT:

SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1

Am I missing something here? Is there an simpler way to do this?

Any help will be appreciated.

Steve D.

You are correct on the problem, but I am certain you can do it a little easier. Try this example:

DECLARE @.v_test TABLE ([num] INT)

INSERT INTO @.v_test VALUES(1)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

SELECT AVG(CONVERT(DECIMAL,num))

FROM @.v_Test

|||

The problem is that I need the resultant value to be an integer, not a decimal value. The query from above will return 1.75000.

I need the result back as an integer, which was why I had to cast it back to an integer after adding .5. Just looking for a simpler way to do this

|||

I'm not sure I understand. Would your answer be rounded to 2? Converting it to an INT is what it is doing for you.

You could use this to round it. If you want to recast it to INT, that is possible at this point.

SELECT ROUND(AVG(CONVERT(DECIMAL,num)),0)

|||

OR

SELECT CEILING(AVG(CONVERT(DECIMAL,num))) from yourTable

|||

More info.

I want the Average value to round to the nearest integer.

1.8 would round to 2

1.2 would round to 1

Ceiling looks like it would make 1.2 convert to 2.

Edit-

Tested this further- it looks like ROUND is what I need. It returns a decimal number (ex 1.0000), but I can convert that back to int.