CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Red
Red

Reputation: 3267

SQL 1 Digit Before The Decimal Place

I'm storing a ratio of a total in a table and therefore know I'll only have values between 0 and 1, and 4 decimal places are sufficient precision. Therefore I've created the table column as DECIMAL(5,4).

When calculating the data for population I have found a strange behaviour where SQL seems to want an extra 2 places before the decimal, so if I use DECIMAL(5,4) or DECIMAL(6,4) I get an Arithmetic overflow error converting int to data type numeric. error. I have to push it to DECIMAL(7,4) for one side of the divide, then wrap the whole divide in another cast to get the precision I actually require.

SQL considers every different precision a different datatype, invoking a conversion, so I'm conscious of the workload this is adding when done at scale as I can't cast straight to the DECIMAL(5,4) I'm storing it as. I'm also just curious as to what is happening to cause this.

The example below demonstrates the issue. It should work with DECIMAL(2,1), but it will only work with DECIMAL(3,1). Even then, the results are to 6 decimal places. Can anyone at least explain what's happening here, and hopefully a way to avoid it without doubling up on casts?

DECLARE @SalesTable TABLE (ProductId INT, Size NVARCHAR(2), Quantity INT)
INSERT INTO @SalesTable VALUES (123, 'S', 5), (123, 'M', 20), (123, 'L', 15), (123, 'XL', 10)

SELECT Size, sales.Quantity / CAST(sales.Total AS DECIMAL(3,1)) AS SalesRatio
FROM (
    SELECT Size, Quantity, SUM([Quantity]) OVER (PARTITION BY ProductId) AS Total
    FROM @SalesTable
) AS sales

uses an extra decimal and produces a result to a different precision

Size    SalesRatio
S       0.100000
M       0.400000
L       0.300000
XL      0.200000

Upvotes: 0

Views: 1425

Answers (2)

SteveC
SteveC

Reputation: 6015

The decimal precision should be set to the maximum length needed to contain the largest number included in the calculation (unless otherwise dealt with). In this case Quantity is data type INT which has min/max of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). To be covered in all situations you could make the data type Decimal(16, 4). 12 digits for any integer + 4 places to the right of the decimal point. SQL Server with automatically expand the decimal precision if you add or multiply 1.0 to the denominator of a quotient. Something like this

DECLARE @SalesTable TABLE (ProductId INT, Size NVARCHAR(2), Quantity INT)
INSERT INTO @SalesTable VALUES (123, 'S', 5), (123, 'M', 20), (123, 'L', 15), (123, 'XL', 10)

SELECT Size, cast(sales.Quantity / (sales.Total*1.0) AS DECIMAL(16,4)) AS SalesRatio
FROM (
    SELECT Size, Quantity, SUM([Quantity]) OVER (PARTITION BY ProductId) AS Total
    FROM @SalesTable
) AS sales;

Output

Size    SalesRatio
S       0.1000
M       0.4000
L       0.3000
XL      0.2000

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

I don't really understand your confusion. In your example, salesTotal1 has a value of 50. This requires two digits to the left of the decimal place.

Perhaps the confusion is on internal types for decimals. Although the rules are complicated for multiplication and very, very arcane for division, the rules are much simpler for addition: the type doesn't change.

So, the total needs to fit in the decimal value.

If you care about the type of the result of an arithmetic operation, then convert the entire expression:

SELECT Size,
       CAST(sales.Quantity * 1.0 / sales.Total AS DECIMAL(3,1)) AS SalesRatio

The * 1.0 may not be necessary, but it makes sure that the division has decimal places.

Upvotes: 1

Related Questions