Float values UNION ALL query

Question

I have two ms sql server tables with column of type float (not null).

when I do union ALL in those two tables

SELECT [float_column], 
       Min(sourcename) AS ExistsInFile 
FROM   (SELECT [float_column], 
               'File 1' AS SourceName 
        FROM   table1 
        WHERE  column1 = 'abc' 
        UNION ALL 
        SELECT [float_column], 
               'File 2' AS SourceName 
        FROM   table2 
        WHERE  column1 = 'abc') x 
GROUP  BY [float_column] 
HAVING Count(DISTINCT sourcename) <> 2

it gave me following output, but it should not show any difference as both values are same.

float_Column     |    ExistsInFile
-53590187.62            File 2
-53590187.62            File 1

Is this because of float type? Any help would be appreciated!

Floats are approximate, they don't always store an exact value. This is why you should not rely on comparing them in a group by or where clause. — Tim Lehner, Jul 08 '16 at 21:17
More info on using these datatypes in SQL Server: [Using decimal, float, and real](https://msdn.microsoft.com/en-us/library/ms187912.aspx) — Tim Lehner, Jul 08 '16 at 21:24

Gordon Linoff · Accepted Answer · 2016-07-08T21:22:04.407

Yes, the problem is the float. The issue here is not the union all. The issue is the group by.

Floating point numbers can differ the the smallest bits representing the number -- and you can't see the differences. So, for your problem, just convert everything to a decimal:

SELECT CAST([float_column] as DECIMAL(12, 2), 
       Min(sourcename) AS ExistsInFile 
FROM   (SELECT [float_column], 
               'File 1' AS SourceName 
        FROM   table1 
        WHERE  column1 = 'abc' 
        UNION ALL 
        SELECT [float_column], 
               'File 2' AS SourceName 
        FROM   table2 
        WHERE  column1 = 'abc') x 
GROUP BY CAST([float_column] as DECIMAL(12, 2) 
HAVING Count(DISTINCT sourcename) <> 2 ;

Thanks, I will test it and mark as answer. Have a nice rest of the day! — Relativity, Jul 08 '16 at 21:22

Float values UNION ALL query

1 Answers1