DIVISION ALWAYS RETURNS 0 - 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

General SQL Server Forums

New to SQL Server Programming

DIVISION ALWAYS RETURNS 0

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2007-12-11 : 08:13:32

It is common for me to need to create ratios from data in my database such as


SELECT
    ( list_value / sale_price ) as ratio
FROM
    values

The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned.
BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?

doco

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-11 : 08:14:57

Try this:

SELECT
( list_value * 1.0 / sale_price ) as ratio
FROM
values

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-11 : 08:16:22

are list_value and sale_price integers?

SELECT
( cast(list_value as DECIMAL(18,8)) / cast(sale_price AS DECIMAL(18, 8)) ) as ratio
FROM
values

Duane.