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.

Answer 1

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 .