SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 AVG is rounding ... stop it
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

112 Posts

Posted - 04/29/2013 :  19:07:09  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/29/2013 :  19:25:35  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 04/29/2013 :  19:38:14  Show Profile  Reply with Quote
Thanks so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/30/2013 :  04:34:50  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
370 Posts

Posted - 04/30/2013 :  12:20:19  Show Profile  Reply with Quote
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.

Edited by - ScottPletcher on 04/30/2013 12:23:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000