The issue you're facing is due to how SQL Server handles floating-point numbers.

1. Floating-Point Precision:

• Floating-point numbers (like FLOAT in SQL Server) can have small rounding errors because they are stored in a way that approximates real numbers. This means that even if a sum looks like 1, it might actually be something like 0.9999999 or 1.0000001 internally.

2. HAVING Clause:

• When you use HAVING SUM(R.Valor) <> 1, SQL Server checks the exact value of the sum. Due to the small rounding errors, the sum might not be exactly 1, even if it appears to be.

3. Using ROUND:

• By using ROUND(SUM(RP.Valor), 0), you round the sum to the nearest whole number, which eliminates the small differences caused by floating-point precision. This is why your query works when you use ROUND.

In summary, the difference arises because floating-point numbers are not always exact, and the HAVING clause checks for exact values. Using ROUND helps to avoid this issue by rounding the sum to a whole number.