CODEWITHSUNDEEP
sqlsql-servert-sql
dmoney
dmoney

Reputation: 881

Division of two values returning 0 rather than positive or negative decimal

I have a large stored procedure updating a table each night. It grabs a basic amount of information in to a temp table before updating each of the remaining columns through a series of updates.

One of these updates is a division. The division takes two previously updated fields and divides them.

UPDATE C
SET C.RETURN_ON_SALES = C.TW_UNIT_SALES / C.DISTRIBUTION
FROM
    #custTop150 C

The column definitions in #custTop150 are as follows:

RETURN_ON_SALES DECIMAL(5,2) NULL
TW_UNIT_SALES INT NULL
[DISTRIBUTION] INT NULL

All of my results for this update are returning as 0.00. To give you an example of some of the divisions it is calculating:

7 / 41   | Returns 0.00 when it should return 0.17073170731
11 / 41  | Returns 0.00 when it should return 0.26829268292
9 / 41   | Returns 0.00 when it should return 0.21941219512
5 / 38   | Returns 0.00 when it should return 0.13157894736

I know that the answer will be pretty simply or right in front of my eyes but at this point in time I am completely lost as to why this is happening.

Thanks

Upvotes: 0

Views: 1011

Answers (3)

Bibin Mathew
Bibin Mathew

Reputation: 465

Guess you are working on SQL Server. Then change RETURN_ON_SALES to Decimal(10,10) and CAST the inputs or change its data type to DECIMAL

 DECLARE @RETURN_ON_SALES decimal(10,10);
 DECLARE @TW_UNIT_SALES INT ;
 DECLARE @DISTRIBUTION INT ;

 SET @TW_UNIT_SALES=7;
 SET @DISTRIBUTION=41;
 SET @RETURN_ON_SALES=CAST(@TW_UNIT_SALES AS DECIMAL (10,5))/ CAST (@DISTRIBUTION AS DECIMAL(10,5));
 PRINT @RETURN_ON_SALES;

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: It seems your columns are defined as INTEGER and in the division it should return in DECIMAL value so for this we can simply change the one of operand value to DECIMAL using CAST and it will return the DECIMAL value as below

DECLARE @first INT = 4, @second INT = 3
SELECT @first/@second -- OUTPUT: 1
SELECT @first/CAST(@second AS DECIMAL(10,5)) -- OUTPUT: 1.33333333333333

Upvotes: 1

FDavidov
FDavidov

Reputation: 3675

Try this:

UPDATE C
  SET C.RETURN_ON_SALES = 1.0 * C.TW_UNIT_SALES / C.DISTRIBUTION
FROM
    #custTop150 C

Note that the 1.0 will cause a cast to float before assigning to the target variable.

You are not specifying which DBMS so, to make sure it works, you may also try the following (if the previous still does not work):

UPDATE C
  SET C.RETURN_ON_SALES = (1.0 * C.TW_UNIT_SALES) / (1.0 * C.DISTRIBUTION)
FROM
    #custTop150 C

Upvotes: 0

Related Questions