CODEWITHSUNDEEP
sqlt-sql
Steve Cross
Steve Cross

Reputation: 99

How can I get a decimal result into a SQL Server table when dividing two integers?

This is what I am currently doing:

CREATE TABLE #compareDiffs
(
    groupAPercentDifference DECIMAL
)

DECLARE @groupaprev AS int
DECLARE @groupacurr AS int

SET @groupaprev = 144
SET @groupacurr = 114

INSERT INTO #compareDiffs
    SELECT (@groupaprev - @groupacurr) / CAST(@groupaprev AS DECIMAL (7,2))

--Select #1
SELECT * FROM #compareDiffs

--Select #2
SELECT (@groupaprev - @groupacurr) / CAST(@groupaprev AS DECIMAL (7,2))

groupAPercentDifference
0

(No column name)
0.20833333

Select #1 (the value in the table) always returns 0. Select #2 (straight evaluation) returns .20833333 which is what I want in the table.

This looks straightforward but I must be missing a conversion somewhere. Can anyone assist? Thanks in advance.

Upvotes: 1

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This declaration:

CREATE TABLE #compareDiffs (
    groupAPercentDifference DECIMAL
);

Defines a column with a scale of 0 and a default precision of 18 -- no digits after the decimal, 18 digits before. I would suggest something like:

CREATE TABLE #compareDiffs (
    groupAPercentDifference DECIMAL(10, 4)
);

But perhaps you want a floating point representation.

Upvotes: 3

Related Questions