## Average rounding down--help

### Average rounding down--help

I am running a query to average the following numbers (Field Type is
Integer):
4
5
8
8
8
8
9
9
10
10
10
10
10
10

The query is returning an average of 8 instead of 8.53...any ideas?

Select Avg(lQ3) As AverageQ3 From tblStats Where....

Thanks

### Average rounding down--help

Quote:> I am running a query to average the following numbers (Field Type is
> Integer):
>       4
>       5
>       8
>       8
>       8
>       8
>       9
>       9
>       10
>       10
>       10
>       10
>       10
>       10

> The query is returning an average of 8 instead of 8.53...any ideas?

Because 199/14=8
At least in Integer arithmetic.

AVG(intCOlumn) is required to return an int.
If this isn't what you want you need to
AVG(numericColumn)

look here:

set nocount on
go
drop table tblStats
go
create table tblStats (lQ3 int )
go

insert into tblStats(lQ3) values(4)
insert into tblStats(lQ3) values(5)
insert into tblStats(lQ3) values(8)
insert into tblStats(lQ3) values(8)
insert into tblStats(lQ3) values(8)
insert into tblStats(lQ3) values(8)
insert into tblStats(lQ3) values(9)
insert into tblStats(lQ3) values(9)
insert into tblStats(lQ3) values(10)
insert into tblStats(lQ3) values(10)
insert into tblStats(lQ3) values(10)
insert into tblStats(lQ3) values(10)
insert into tblStats(lQ3) values(10)
insert into tblStats(lQ3) values(10)

Select
Avg(lQ3)As AverageQ3,
sum(lQ3) as SumQ3,
count(lQ3) as CountQ3,
sum(lQ3)/count(lQ3) as AverageQ3_computed,
cast(sum(lQ3) as numeric)/count(lQ3) as AverageQ3_computed_converted,
Avg(cast(lQ3 as numeric))As AverageQ3_converted

from tblStats

-------

AverageQ3,SumQ3,CountQ3,
AverageQ3_computed,AverageQ3_computed_converted,AverageQ3_converted
8,  119,     14,                  8,               8.50000000000,
8.500000

David

### Average rounding down--help

You could also cast the column to a numeric:
Select Avg(CAST(IQ3 AS numeric(6,2)))

Bob

Quote:> I am running a query to average the following numbers (Field Type is
> Integer):
>       4
>       5
>       8
>       8
>       8
>       8
>       9
>       9
>       10
>       10
>       10
>       10
>       10
>       10

> The query is returning an average of 8 instead of 8.53...any ideas?

>  Select Avg(lQ3) As AverageQ3 From tblStats Where....

> Thanks

Hi there...

If I use the SQL ROUND(numeric_expression, length) function in my queries,
this happens:

ROUND(0.4 ,1) --> 0
ROUND(0.5 ,1) --> 0
ROUND(0.6 ,1) --> 1

WHY!!! is 0.5 rounded down, and how can I change this!!!

Could this be a diff. between european and US standards??