AVG is rounding ... stop it - SQL Server Forums

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

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-29 : 19:38:14

Thanks so much!

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources