0

Due to confidentiality, I will not post the table, but will provide as much detail as I can if someone needs more information.

I am doing a "SELECT AVG(result)" from a table and when I use this query on mysql workbench, the result is something like 99.78999999999999. However when I try to use mysql connector in python, as soon as I do a 

results = cursor.fetchall()

and I check the results, the value is 99.79. It rounds it by itself and I'm not sure why.

EDIT

99.7, 99.9, 99.8, 99.85, 99.6, 99.85, 99.85, 99.75, 99.8, 99.8 Which averages to 99.78999999999999 in mysql workbench but 99.79 in python mysql-connector

rominoushana
  • 451
  • 2
  • 4
  • 17
  • What is the type of result in the database? – Daniel Jan 08 '20 at 20:10
  • @Daniel Thanks for the reply. I am performing an AVG operation on a column that is of DOUBLE type so I'm assuming the select AVG(result) will also result in double? – rominoushana Jan 08 '20 at 20:16
  • and if you convert the result into a text ..? – GiovaniSalazar Jan 08 '20 at 20:16
  • @GiovaniSalazar I could try that, but why would I convert to text? All my results are double values. – rominoushana Jan 08 '20 at 20:20
  • I just want to see how is the behavior – GiovaniSalazar Jan 08 '20 at 20:21
  • @GiovaniSalazar Shoot. Unfortunately I do not have permission to change datatype. Sorry! Any other suggestions? – rominoushana Jan 08 '20 at 20:24
  • 92.78999999999999 is the single precision representation of the double 92.79. So somehow calculation is done in single precision. – Daniel Jan 08 '20 at 20:27
  • @Daniel Yes I figured that much, but don't quite understand why it is happening. – rominoushana Jan 08 '20 at 20:36
  • try with an CAST DECIMAL – GiovaniSalazar Jan 08 '20 at 21:06
  • @Daniel How did you get that it's the single precision representation of the double 92.79? I can't reproduce that. – Stefan Pochmann Jan 08 '20 at 21:06
  • @GiovaniSalazar Well the problem is, like I said, it shows up correctly in mysql workbench, but when I try to print out the value in python with mysql connector, it is rounding the value to two decimal places. – rominoushana Jan 08 '20 at 21:38
  • @rominoushana Wait, are you saying `92.78999999999999` is correct? What numbers do you have such that their average is that? – Stefan Pochmann Jan 08 '20 at 21:41
  • @StefanPochmann Yes it is correct. When I retrieve the average value in python, I want to be able to get the same value that I get in mysql workbench which is 92.78999999999. Instead I'm getting a rounded of value. I am averaging numbers that are like 7 or 8 significant digits – rominoushana Jan 08 '20 at 21:44
  • @rominoushana Can you share the numbers? Might provide some insight. – Stefan Pochmann Jan 08 '20 at 22:05
  • @StefanPochmann I would love to, but there are just too many numbers to share. However, just a note, I think your missing the point of the question. It is not that the average is wrong. It is that python mysql connector is rounding off the average and I'm losing precision.If you still think the numbers are necessary, then I will see the best way to post them – rominoushana Jan 08 '20 at 22:08
  • 1
    I don't see how we can realistically expect to solve this when we have essentially no information to work with. – AMC Jan 08 '20 at 22:13
  • @AMC I apologize. Here are the results: 99.7, 99.9, 99.8, 99.85, 99.6, 99.85, 99.85, 99.75, 99.8, 99.8 Which averages to 99.78999999999999 in mysql workbench but 99.79 in python mysql-connector. Let me know if you need anything else – rominoushana Jan 08 '20 at 22:16
  • @rominoushana So you prefer the wrong value? – Stefan Pochmann Jan 08 '20 at 22:20
  • @StefanPochmann I'm not sure why you are saying it is the wrong value? It's not wrong. It just had more precision – rominoushana Jan 08 '20 at 22:20
  • @rominoushana The average of those numbers is 99.79, not 99.78999999999999. – Stefan Pochmann Jan 08 '20 at 22:21
  • @StefanPochmann Oh. Then why is mysql workbench giving me that number. So mysql workbench is giving a wrong value? – rominoushana Jan 08 '20 at 22:23
  • @rominoushana My point was that the number of potential causes is enormous, and we have no way of examining them ourselves. For all we know this is happening because it's Thursday, you're using Newfoundland Standard Time, and your computer's keyboard language is set to esperanto. – AMC Jan 08 '20 at 22:23
  • 1
    @rominoushana https://stackoverflow.com/q/588004/1672429 – Stefan Pochmann Jan 08 '20 at 22:24
  • @StefanPochmann Thank you. I guess that solves it :) – rominoushana Jan 08 '20 at 22:26

1 Answers1

0

As @StefanPochmann mentions in the comments, the average output that mysql workbench is incorrect. Here is an explanation on why: stackoverflow.com/q/588004/1672429

rominoushana
  • 451
  • 2
  • 4
  • 17