Math Division in Sql - 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)

Math Division in Sql

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-06-14 : 14:58:02

Hi Gurus,
When I use below sql statement I get '0' in the resultset...both columns are intergers but not sure why I am getting zero.

select CASE WHEN HeavyCommercialCount = 0 THEN 0
ELSE count1/count2 END AS Percent
from Count

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-14 : 15:05:46

Since they are both integers any division less than 1 will round to zero. There's a nice shortcut to fix this though:

select CASE WHEN HeavyCommercialCount = 0 THEN 0
ELSE count1/count2/1.0 END AS Percent
from Count

Sachin.Nand

2937 Posts

Posted - 2010-06-14 : 15:44:13

Shouldn't it be this way?
count1*1.0/count2*1.0

Just try this select 1/5/1.0

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 02:29:02

quote:
Originally posted by Idera

Shouldn't it be this way?
count1*1.0/count2*1.0

Just try this select 1/5/1.0

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

You just need

count1*1.0/count2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources