Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 AVG is rounding ... stop it

Author  Topic 

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-29 : 19:07:09
Here is my query
Select userid, COUNT(userid)as count, cast(avg(rating)AS decimal(10,2)) as avg from userratings_all
group by UserID

this is rounding the numbers as wholes like:

userid count avg
weqweq 31 4.00
ewewee 51 4.00
jkuudd 32 2.00
juddes 1 3.00


I need the decimals to show how can I get SQL to output the real value?

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-29 : 19:25:35
declare @t1 table (c1 int)

insert into @t1 values (1)
insert into @t1 values (4)
insert into @t1 values (3)
insert into @t1 values (6)
insert into @t1 values (9)
insert into @t1 values (12)


SELECT AVG( c1 * 1.0)
from @t1

-Chad
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-29 : 19:38:14
Thanks so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-30 : 04:34:50
see reason here

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-04-30 : 12:20:19
Select
userid, COUNT(userid)as count,
avg(cast(rating AS decimal(10,2))) as avg
from userratings_all
group by UserID


The * 1.0 will give you only one decimal place; you could use 1.00 if you wanted. I personally prefer to explicitly state the type, particularly if I already know the specific data type I want.
Go to Top of Page
   

- Advertisement -