SQL Server appears to round incorrectly

Question

I have a table which stores course results. A course may have more than one exam, each with its own weighting.

In this example, I have 2 exam marks for a student, which are then weighted to give the course mark. Both the mark and the weighting are stored in FLOAT columns.

Here's my code to extract the exam marks:

WITH RawData AS 
(
    SELECT 
        lngRelatedScoreID AS [ID],
        cc.strName AS Exam,
        cc.dblWeighting, 
        sci.dblModeratedComponentScorePercent AS Mark
    FROM 
        tblStudentComponentInformation sci 
    INNER JOIN 
        tblCourseComponents cc ON sci.lngExamID = cc.lngExamID
    WHERE 
        sci.lngRelatedScoreID IN (73652)
)
SELECT * FROM RawData

The results show as follows:

ID	Exam	dblWeighting	Mark
73652	Flight Dynamics and Control Exam	0.75	0.905
73652	Flight Dynamics and Control Coursework	0.25	0.92

I now combine the two rows, multiplying the weighting by the mark:

WITH RawData AS 
(
    SELECT 
        lngRelatedScoreID AS ID,
        cc.strName AS Exam,
        cc.dblWeighting, 
        sci.dblModeratedComponentScorePercent AS Mark
    FROM 
        tblStudentComponentInformation sci 
    INNER JOIN 
        tblCourseComponents cc ON sci.lngExamID = cc.lngExamID
    WHERE 
        sci.lngRelatedScoreID IN (73652)
)
SELECT 
    [ID], 
    SUM(Mark * dblWeighting) AS TotalWeightedMark
FROM 
    RawData
GROUP BY 
    [ID]

which returns the following - as expected:

ID	TotalWeightedMark
73652	0.90875

However, I want the result to 4 decimal places, so when I multiply the mark by the rounding, and sum the result, I add in the ROUND function:

WITH RawData AS 
(
    SELECT 
        lngRelatedScoreID AS ID,
        cc.strName AS Exam,
        cc.dblWeighting, 
        sci.dblModeratedComponentScorePercent AS Mark
    FROM 
        tblStudentComponentInformation sci 
    INNER JOIN 
        tblCourseComponents cc ON sci.lngExamID = cc.lngExamID
    WHERE 
        sci.lngRelatedScoreID IN (73652)
)
SELECT 
    [ID], 
    ROUND(SUM(Mark * dblWeighting), 4) AS TotalWeightedMark
FROM 
    RawData
GROUP BY 
    [ID]

And here's what I get back:

ID	TotalWeightedMark
73652	0.9087

My question is why this appears to be truncating rather than rounding, given that I've not specified anything other than the default value for the final parameter of the ROUND function.

I wondered if it's because of using FLOAT rather than DECIMAL for the columns, but in this case there isn't any rounding required in the calculations, except for the one calculation where I've specified to round from 5 digits to 4.

Can anyone advise?

In case it's relevant, I'm using SQL Server 2017.

Thanks.

If you want precise values, why are you using a floating point value? — Thom A, Sep 06 '21 at 11:16
Note the word "approximate" in the [float documentation](https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql). — Dan Guzman, Sep 06 '21 at 11:24
@Larnu - how do you get (0.905*0.75)+(0.92*0.25) to equal less than either of the marks?! And to answer your question, this is a database that I've inherited with these column definitions. As I said in my post, I understand that there can be rounding errors in calculations with FLOAT cols, but there shouldn't be any rounding going on in a simple calculation such as this. — Andrew Richards, Sep 06 '21 at 11:24
It's not a SQL or SQL Server behaviour. It's a mis-understanding of how floats work; floats are not magically infinitely precise, they're base-2 ***approximations*** of base-10 numbers *(the base 10 numbers that you're using **do not exist** in base-2, and so have to be approximated, thus you get rounding 'errors')*. Search online for explanations of floating point behaviour, then use decimals. — MatBailie, Sep 06 '21 at 11:25
Essential reading --> [What Every Computer Scientist Should Know About Floating-Point Arithmetic](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html) — Jamiec, Sep 06 '21 at 11:25
@AndrewRichards, run this from a command-prompt: `sqlcmd -Q "SELECT CAST(0.905 AS float);"`. The result is `0.90500000000000003`. — Dan Guzman, Sep 06 '21 at 11:27
Nothing is "simple" when you're using floating point values. But there is most certainly rounding going on. `0.90875` as a floating point value is actually `0.908749999999999946709294817992486059665679931640625`. For *display* purposes this is rounded up to `0.9875`. This is why your value is rounded **down** when you apply `ROUND`, not up, as it's actual value is (just) less than `0.90875`. — Thom A, Sep 06 '21 at 11:28
Whether a calculation is "simple" in base 10 is unfortunately not relevant when we're talking base 2 floating point. The extremely simple value `0.1` is already not exactly representable. — Jeroen Mostert, Sep 06 '21 at 11:33
Okay - thanks - I understand. Will read through that article @Jamiec - thanks. — Andrew Richards, Sep 06 '21 at 11:35
Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Charlieface, Sep 06 '21 at 12:20
@Charlieface - thanks - that's helpful, and I'll definitely steal the pizza-cutter analogy when explaining this to other people! — Andrew Richards, Sep 07 '21 at 12:12

Thom A · Accepted Answer · 2021-09-06T12:12:51.453

As has been mentioned in the comments, the "problem" is the data type, not the expression.

If we take the below example:

SELECT [ID],
       SUM(FloatWeighting*FloatMark) AS Float,
       SUM(Decimalweighting*DecimalMark) AS Decimal,
       CONVERT(decimal(18,12),SUM(FloatWeighting*FloatMark)) AS ConvertedFloat,
       ROUND(SUM(FloatWeighting*FloatMark),4) AS RoundedFloat,
       ROUND(SUM(Decimalweighting*DecimalMark),4) AS RoundedDecimal
FROM (VALUES(73652,'Flight Dynamics and Control Exam      ',CONVERT(float,0.75),CONVERT(decimal(3,2),0.75),CONVERT(float,0.905),CONVERT(decimal(4,3),0.905)),
            (73652,'Flight Dynamics and Control Coursework',CONVERT(float,0.25),CONVERT(decimal(3,2),0.25),CONVERT(float,0.92),CONVERT(decimal(4,3),0.92)))V(ID,Exam,FloatWeighting,DecimalWeighting,FloatMark,DecimalMark)
GROUP BY ID;

If you run this, you get the following results:

ID    Float   Decimal ConvertedFloat RoundedFloat RoundedDecimal
----- ------- ------- -------------- ------------ --------------
73652 0.90875 0.90875 0.908750000000 0.9088       0.90880

Notice that the rounded decimal value provides the value you expect, but the rounded float does not. This is because, as discussed in the comments, the floating point value isn't a base 10 value, but a base 2 value. As a result the float value 0.90875 isn't actually 0.90875, it's 0.908749999999999946709294817992486059665679931640625. Notice that this value is actually (just) less than 0.90875.

Because of this when you apply the function ROUND the value above is used, and when you ROUND that value to 4 decimal places then 0.9087 is the correct answer.

The real solution here, therefore, is to fix your design and ALTER your table to make the columns a decimal. As an example, that might be:

ALTER TABLE dbo.tblCourseComponents ALTER COLUMN Mark decimal(4,3);

Note that you would need to use an appropriate scale and precision for your actual values.

If you can't change the data type, or at least not right now, then you can explicitly convert the value to a decimal first before your arithmetic. For example:

ROUND(SUM(CONVERT(decimal(3,2),Weighting)*CONVERT(decimal(4,3),Mark)),4)

Again, ensure you use appropriate precisions and scales for your data .

SQL Server appears to round incorrectly

1 Answers1