SQL: Having MAX <> SUM

Question

It's actually a very simple query, but I don't know why it's showing me values that are actually excluded with the HAVING.

---

Table 1 -> Maintable

• ExpenditureComplete -> Float
• ID

Table 2 -> Subtable (more then 1 Entry for Maintable)

• DurationHour
• MainTable_ID (link to ID of Table1)
• ID

SELECT  
            a.ID, 
            b.MainTable_ID,
            MAX(a.ExpenditureComplete) as ExpenditureHours, 
            SUM(b.DurationHour) as DurationHours

    FROM [Table_1] as a
            Inner join
                [Table_2] as b
                    On
                        a.ID = b.MainTable_ID 

    Where a.Inactive = 0 
          and b.Inactive = 0

    Group by
                a.ID, 
                b.MainTable_ID

    Having MAX(a.ExpenditureComplete) <> SUM(b.DurationHour)

Answer 1

Try this:

Having ROUND(MAX(a.ExpenditureComplete),2) <> ROUND(SUM(b.DurationHour),2)

Answer 2

It tends to become a nuisance with decimals and the use of SUM etc.

This should be possible (although I can't test it at the moment):

WITH Aggregated AS (SELECT  
        a.ID, 
        b.MainTable_ID,
        MAX(a.ExpenditureComplete) as ExpenditureHours, 
        SUM(b.DurationHour) as DurationHours

FROM [Table_1] as a
        Inner join
            [Table_2] as b
                On
                    a.ID = b.MainTable_ID 

Where a.Inactive = 0 
      and b.Inactive = 0

Group by
            a.ID, 
            b.MainTable_ID)

SELECT ID, Maintable_ID, ExpenditureHours, DurationHours
FROM Aggregated
WHERE ExpenditureHours <> DurationHours

Answer 3

You are comparing the result of at least one FLOAT value to a fixed point value or to another FLOAT.

Floating point arithmetic is dark art and mostly magic, it has it's own limitations and problems, most notably rounding errors.

Depending on the underlaying data, you might see similar values, but in the background they are slightly different.

When you do mission critical comparison on numeric values, do them using a fixed point data type.

As this is described on https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187912(v=sql.105)?redirectedfrom=MSDN and in the question SQL Server rounding Error, Giving different values

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

Rounding the values may help in some scenarios, but it will give you nasty surprises in the future.