How to round numbers properly in SQL Server?

Question

Below picture example, is there a way to make Total Cost v1 precisely equal to v2 in SQL Server?

I have a simple sales report.

• Cost have 2 decimals or 4 decimals.
• Count v2 is just the sum of Count v1 of each unique item.

My formula of Total Cost v1 in SQL Server is:

SUM(ROUND(([Cost]*(CAST([Count] as INT))), 2)) as 'Total Cost v1'

Is there a way to modify this above formula to let the final total match the v2? Otherwise if the list becomes big, bigger difference on the numbers.

The data type for Count is decimal(10, 4). The datatype for Cost is money.

Answer 1

You should change your formula to

ROUND(SUM([Cost]*(CAST([Count] as INT))), 2) as 'Total Cost v1'