Select a,b,a/b retrurn int not real in last column

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

General SQL Server Forums

New to SQL Server Programming

Select a,b,a/b retrurn int not real in last column

Author

Topic

romeck
Starting Member

16 Posts

Posted - 2013-08-15 : 14:46:14

Hello
i have table
x y z
a,10,10
b,10,3
a,3,2
and as i use:
select x, sum(y), sum(z), sum(y)/sum(z)
i have:
a,13,12,1 (instead of 1,08...)
b,3,2,1 (instead of 1.5)

What should i do to have proper real value instead of int one ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 16:12:46

This is because of integer division. Force at least one of the pieces - numerator or denominator to non-integer type. For example any of these

select x, sum(y), sum(z), 1.0*sum(y)/sum(z)
select x, sum(y), sum(z), CAST(sum(y) AS FLOAT)/sum(z)
select x, sum(y), sum(z), sum(y)/sum(z*1E)

Usually I also add a NULLIF condition to avoid division by zero - so for example:

select x, sum(y), sum(z), 1.0*sum(y)/NULLIF(sum(z),0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-18 : 11:46:09

This may help you to understand why it happens http://beyondrelational.com/modules/2/blogs/70/posts/10825/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