Dividing by two queries resulting in zero, TSQL

Question

I am trying to get a ratio from the following statement:

 Select ((query1)/(query2)) AS Ratio.

The Result is 0. the actual result is .34 So SQL is rounding to zero. How can I fix this?

integer division. Cast one of the sides to `numeric` or `float` — Martin Smith, Feb 06 '14 at 15:00
When I try the statement: SELECT((5)/(10)) the result is still 0. Each query results in a number, but division blows the equation up — ColorfulWind, Feb 06 '14 at 15:08
Yes they are both integers. The numerator and or denominator need to be non integer to avoid integer division (which only returns the "whole" part and ignores the remainder). Did you look at the duplicate? — Martin Smith, Feb 06 '14 at 15:10
What duplicate? Also how do I change integer form to non integer form? — ColorfulWind, Feb 06 '14 at 15:17
`SELECT ( cast(( query1 ) AS NUMERIC(16, 6)) / ( query2 ) ) AS Ratio ` — Martin Smith, Feb 06 '14 at 15:21

score 1 · Accepted Answer · answered Feb 06 '14 at 15:30

Select( 
       (SELECT 8) --<-- Query 1 returns INT 8
              / 
       (SELECT 5) --<-- Query 2 returns INT 5
      )  AS Ratio

Because both values are INT but when divided you get a decimal value back, Sql Server does an implicit conversion to INT and truncates any Decimal point and returns a whole number back.

Above query returns 1

Select( 
       CAST((SELECT 8)AS NUMERIC(18,5)) --<-- Query 1 CAST as a DECIMAL or NUMERIC
              / 
       CAST((SELECT 5) AS NUMERIC(18,5)) --<-- Query 2 CAST as a DECIMAL or NUMERIC
      )  AS Ratio

When converted to Numeric datatype results retuned by both queries sql server does an implicit coversion on the result returned by dividing both queries and returns

Result : 1.60000000000000000000

Dividing by two queries resulting in zero, TSQL

1 Answers1