| Author |
Topic |
|
archana23
Yak Posting Veteran
89 Posts |
 Posted - 2012-05-30 : 15:10:36
|
| Hello,I am new to sql queries in my query i am calculating Average by using select Sum(b.items)/COUNT(Distinct a.ServiceDateTime) as AvgFor example if answer is 5.8 above expression returning 5 but i want to show 6.Like 5.1 -- need to show 5 5.7 -- need to show 6 Can you help me on this pleaseThanksArchana |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-30 : 15:41:11
|
| casting it to int also will provide you with required result. ROUND will still preserve the decimal part------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-31 : 09:23:52
|
[code]select convert(int, Sum(b.items) * 1.0 / COUNT(Distinct a.ServiceDateTime)) as Avg[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2012-05-31 : 09:41:01
|
| Hello Khtan,Thank you for your reply.Actually my requirement is to round the values to int as 5.1 to 5.5 -- as 55.6 to 5.9 -- as 6but above expression is converting 5.7 to 5 which is not correct.Can you please help me on this..Thank you.Archana |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-31 : 09:47:33
|
then round( < value > , 0) it first before convert
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-05-31 : 13:17:10
|
| [code]DECLARE @Foo FLOAT = 5.6667;SELECT ROUND(@Foo, 0)[/code]Works jsut fine. Can you show some sample data and expected output? It looks like your formual has issues, meaning you are doing INTeger math and not decimal math or your are rounding the the wrong palce.EDIT: Quck guess: [code]Round((Sum(b.items) * 1.0) / COUNT(Distinct a.ServiceDateTime),0)[/code] |
|
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2012-05-31 : 14:58:52
|
| Hello Lamprey,Thank you its working now..Archana |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
Round the Floating values