lnjblue lnjblue - 3 years ago 50
SQL Question

SQL loses decimals after multiplication

I have a table which includes:

COUNT RISK
35 0.6456000000
11 0.5234000000
4 0.8431000000


I need a column to multiply the two columns. However I'm getting the result of:

TOTAL
35
11
4


SQL is clearly rounding up the decimals as 1. I've tried casting as decimal, numeric and multiplying by 1.0. I need to retain the decimals for an actual calculation. Any help would be great

Answer Source

Convert result to decimal like this

SELECT 
   CONVERT(DECIMAL(16,10), COUNT * RISK) AS DecimalResult
FROM dbo.whatever;

Or convert COUNT to decimal

SELECT CAST(COUNT AS DECIMAL(16,10)) * RISK
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download