How to round avg results upwards (int datatype)

Question

How to round avg results upwards (int datatype)

jaimepremy

SSC-Addicted

Points: 413
More actions
April 18, 2009 at 10:10 pm

#208528

Hi there,
I'm having a hard time rounding avg results upwards (underlying dataype is int).
For example if the sum of 3 rows = 8, then avg should be 3 since 8/3 = 2,67. But I get 2.
I would like it to round up if the fraction > 0,5.
The code I use is like this:
ROUND(AVG(Atraso), 0) AS avgAtraso
I also tried: ROUND(CAST(AVG(Atraso)AS FLOAT), 0) AS avgAtraso, but to no avail.
I would like to know how this can be done without having to convert the underlying INT to FLOAT.
I appreciate any help
Regards,
Jaime

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

hayzer Say Hey Kid Points: 713 More actions · Answer 1

jaimepremy (4/18/2009)
Hi there,
I'm having a hard time rounding avg results upwards (underlying dataype is int).
For example if the sum of 3 rows = 8, then avg should be 3 since 8/3 = 2,67. But I get 2.
I would like it to round up if the fraction > 0,5.
The code I use is like this:
ROUND(AVG(Atraso), 0) AS avgAtraso
I also tried: ROUND(CAST(AVG(Atraso)AS FLOAT), 0) AS avgAtraso, but to no avail.
I would like to know how this can be done without having to convert the underlying INT to FLOAT.
I appreciate any help
Regards,
Jaime

Hi jaime!

your code should be like this:

declare @AVG table

(

AVGSUM int

)

insert into @AVG

select 1 union all

select 2 union all

select 5

select round(avg(cast(avgsum as float)),0) from @AVG

BR

Hayzer

arun.sas SSChampion Points: 11831 More actions · Answer 2

Hi,

Try this code

declare @abc table (

slno int,

No1 int

)

insert into @abc values(1,4)

insert into @abc values(2,2)

insert into @abc values(3,2)

select AVG(No1) from @abc

RESULT

2

select AVG(No2) from (select convert(float,No1)No2 from @abc) as X

RESULT

2.6666666666666665

ARUN SAS

Chandra Sekhar Manchukonda SSC Veteran Points: 260 More actions · Answer 3

Chandra Sekhar Manchukonda

SSC Veteran

Points: 260

April 20, 2009 at 12:57 am

#980529

Hi,

can you try with the below code?

CEILING(AVG(Atraso))

Kemp Davis Ten Centuries Points: 1067 More actions · Answer 4

Try this

ROUND(AVG(CAST(Atraso AS FLOAT)), 0) AS avgAtraso

Kemp

jaimepremy SSC-Addicted Points: 413 More actions · Answer 5

HI Kemp, thank u, works like a charm.

Chandra: CEILING(AVG(Atraso)) won't cut it: it rounds down to 2 from 2,67

As for the responses from Hayzer and Arun, I think u guys misunderstood my question.

But many thanks to all of u for your attention

Regards,

Jaime

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 6

Here is some code for review. The three select statements are there to show the progression. Obviously, the final one combines everything.

declare @TBL table

(amt int)

insert into @TBL

select 1 union all

select 3 union all

select 4

select avg(amt * 1.00) as 'AVERAGE' from @TBL

select round(avg(amt * 1.00),2) as 'AVERAGE' from @TBL

select cast(round(avg(amt * 1.00),2) as numeric(7,2)) as 'AVERAGE' from @TBL

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 7

jaimepremy (4/18/2009)
Hi there,
I'm having a hard time rounding avg results upwards (underlying dataype is int).
For example if the sum of 3 rows = 8, then avg should be 3 since 8/3 = 2,67. But I get 2.
I would like to know how this can be done without having to convert the underlying INT to FLOAT.

The answer is no. You will need to CAST to a float (or other datatype that has decimal places).

The reason is that when you divide two integers, SQL will perform "integer division" and truncate the decimal place. This is the same for all programming & scripting languages.

jaimepremy SSC-Addicted Points: 413 More actions · Answer 8

Mike, Goldie

Thank u for your answers and attention, but like I said earlier Kemp provided the solution:

ROUND(AVG(CAST(Atraso AS FLOAT)), 0) AS avgAtraso

regards,