CODEWITHSUNDEEP
sqlcastingdecimal
Fariya Farhad
Fariya Farhad

Reputation: 33

Casting as decimal not acting as expected

I am new to SQL and I am trying to calculate percentage from two pre-calculated numbers. I want the percentage to be at 2 decimal points. The numbers are integers so I am casting them as decimals before the percentage calculation. The calculation is simply

(Risk1/GrandTotal *100) both from the same table.

A piece of the code is given below: 

SELECT risk_date_day,
       ((CAST(Risk1 as decimal(38,2))/CAST(GrandTotal as decimal(38,2))) * 100) FROM DPRAT2_Export

The result shows 6 numbers after the decimal

I have tried many different numbers for the parameters of decimal(x,x). I understood from research that the 2nd number is scale and that specifies the number of places after the decimal. I am not sure why the result has 6 decimal places. Please help. Thanks!

Upvotes: 0

Views: 599

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Do the cast() after the division:

SELECT risk_date_day,
       CAST(Risk1 * 100.0 / GrandTotal as decimal(38, 2))
FROM DPRAT2_Export;

SQL in general and SQL Server in particular has very arcane rules for determining the number of decimal points in the result of mathematical operations on decimals. So, just convert the result.

If there is the risk that GrandTotal might be zero, I would advise:

SELECT risk_date_day,
       CAST(Risk1 * 100.0 / NULLIF(GrandTotal, 0) as decimal(38, 2))
FROM DPRAT2_Export;

This prevents the divide-by-zero error, returning NULL instead.

Upvotes: 2

Related Questions