SQL Server "avg" operator and decimals 2

[JtheRipper]

Hi there,

I have 2 pieces of SQL that I run in Query Analyzer (SQL Server 2005). The first one gives me the correct result - 1.5, the second one not - 1.0.

1:
declare @tmp decimal(10,2)
set @tmp=3
set @tmp=@tmp/2
select @tmp
===> 1.5

2:
select 3/2
===> 1.0

I stumbled upon this when I was doing something like:

select avg(col1) from mytbl.

I noticed that it gave me the wrong result ie
3/2 = 1 and not 1.5.
I tried doing a cast/convert but it did not help.


Any ideas ?

Thanks,
J.

 

[vongrunt]

col1 is probably an integer... try simply:

select avg(1.0*col1) from mytbl

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[JtheRipper]

Nope, does not work. I still get the wrong result.

 

[vongrunt]

How 'bout some sample table data + query + result you expect?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[RAS56]

To me, this looks like a casting problem too. I tried this in SQL2005:

declare @tmp1 int, @tmp2 int
select @tmp1=3, @tmp2=2
select @tmp1/@tmp2
-- results: 1
go

declare @tmp1 decimal(10,2), @tmp2 decimal(10,2)
select @tmp1=3, @tmp2=2
select @tmp1/@tmp2
-- results: 1.5000000000000

 

[SQLDenis]

it should work unless your data is not clean take a look at this

create table #testAverage(value int)
insert into #testAverage
select 3 union all
select 2

select avg(value) from #testAverage   --2
select avg(value * 1.0) from #testAverage  --2.5
select avg(convert(decimal(12,3),value)) from #testAverage --2.5

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

try this instead

declare @tmp1 int, @tmp2 int
select @tmp1=3, @tmp2=2
select @tmp1/(@tmp2*1.0)

you are doing integer math, the result of integer math is an integer

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JtheRipper]

Hi,

Thanks for all the VERY quick replies!

This works now:

create table mytbl(name varchar(20), game int, runs int)

select * from mytbl

insert into mytbl values ('John',2,5)

select cast(avg(runs * 1.0) as decimal(10,1)) from mytbl

Bizarre that you have to multiply with "1.0" to get the decimal. I know I am working with integers, but surely if you want the average it should give you a decimal if there is one. Unless it is as SQLDenis says and it is restricted to only integer answers when working with integers. Maybe MS will improve on this in the next service pack.

 

[SQLDenis]

Tou could do this

create table mytbl(name varchar(20), game decimal(10,1), runs decimal(10,1))

select * from mytbl

insert into mytbl values ('John',2,5)

select avg(runs)  from mytbl

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JtheRipper]

Don't want to change my field type ....
You don't get half a run. hehe

Thanks,
J.