Floating-point means the decimal point can be placed anywhere relative to the significant digits of the number with the actual position being indicated separately. The floating-point (approximate value) types are FLOAT, REAL, and DOUBLE, while the fixed-point (exact value) types are INTEGER, SMALLINT, DECIMAL, and NUMERIC. So, why does SUM using a subquery and the GROUP BY WITH ROLLUP produce different results? Floating-point Approximate Values Versus Fixed-point Exact Values In this context, perhaps calculating hourly payments does not make much sense, but the queries did highlight the rounding differences between the two SELECT statements (4256.65347 5 vs. The reader was calculating employee salaries, but I did not have an identical table to query, so I used the most similar table that I could find, and that was the payments table of the classicmodels sample database: Note: some rows were removed from the Group By With Rollup query to reduce the height of the image. Here are the queries I used to show the discrepancy: Hourly Payments Calculation Using a Subquery Hourly Payments Calculation Using Group By With Rollup In today's blog, I would like to share some of what I learned about floating point rounding in MySQL. This prompted me to go on a journey of discovery. I became aware of potential rounding errors in MySQL when a reader asked me why a couple of similar queries were returning slightly different DECIMAL values in calculations. Moreover, DECIMAL columns can be assigned a precision or scale that could have the potential affect of truncation to the allowed number of digits. The reason is that, no matter how many digits a type can accommodate (the maximum number of digits for DECIMAL is 65!) that number is still fixed. Floating Point Rounding Errors in MySQL by Robert GravelleĪlthough MySQL DECIMAL and NUMERIC data types are both fixed-point values, they are still susceptible to rounding errors.
0 kommentar(er)
