int to dec - 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 2005 Forums

Transact-SQL (2005)

int to dec

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-11-25 : 14:25:08

Hi

I am trying something simple, but I am doing something wrong..

I need a simple average of some int numbers, the output must be a number with 2 decimals like 2,5

thenumber=
1
2
3
4
so average is 2,5 (i get 2.00 as result) -->

select thenumber = convert(decimal(10,2),(sum(thenumber )/count(thenumber ))) from uitleen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-25 : 14:28:48

It has to do with the order of data types. You aren't getting a decimal place since your columns are all ints. We add 1.0 to the equation and now can get back decimals.

select thenumber = convert(decimal(10,2),(sum(thenumber )/(count(thenumber )))*1.0) from uitleen

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-11-25 : 14:40:59

thank you :) but still get the 2 :/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-25 : 15:18:00

select thenumber = sum(thenumber)/(count(thenumber)*1.0)
from uitleen

If that doesn't work, I'll write up a test.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-11-25 : 15:35:34

select thenumber = sum(thenumber)*1.0/(count(thenumber)*1.0)
from uitleen

works :D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-25 : 15:45:00

Cool, glad you got it working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:39:49

quote:
Originally posted by IBoonZ

select thenumber = sum(thenumber)*1.0/(count(thenumber)*1.0)
from uitleen

works :D

You dont need a second 1.0

select thenumber = sum(thenumber)*1.0/count(thenumber)
from uitleen

Also, you should always convert the first expression to decimal
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources