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
 General SQL Server Forums
 New to SQL Server Programming
 Select a,b,a/b retrurn int not real in last column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

romeck
Starting Member

16 Posts

Posted - 08/15/2013 :  14:46:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 08/15/2013 :  16:12:46  Show Profile  Reply with Quote
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)

Edited by - James K on 08/15/2013 16:13:35
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/18/2013 :  11:46:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
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.2 seconds. Powered By: Snitz Forums 2000