How to do a Mysql SUM without exponent?

kenw232 · 12-17-2018, 10:08 PM

I want to do this but without the exponent e16. HOw can I do that? I just want to see the complete number. What is 6.105047099639505e16 equal anyway?

Code:

mysql> select sum(ID) as auto_id_total from Table;    
+----------------------+
| auto_id_total        |
+----------------------+
| 6.105047099639505e16 |
+----------------------+
1 row in set (0.00 sec)

berndbausch · 12-17-2018, 11:28 PM

Probably something like

Code:

select format(sum(ID), '000000000000000') ...

For details, see the documentation.

kenw232 · 12-19-2018, 01:26 AM

Quote:

Originally Posted by berndbausch

Probably something like

Code:

select format(sum(ID), '000000000000000') ...

For details, see the documentation.

thanks, that worked great.

kenw232 · 12-19-2018, 01:48 AM

Any idea why this happens? from mysql:

Code:

mysql> select format(sum(ID), '000000000000000') as auto_id_total from table;
+------------------------+
| auto_id_total          |
+------------------------+
| 61,050,470,996,395,050 |
+------------------------+
1 row in set (0.00 sec)

yet when I SUM the ID column in Excel I get 61050470996395000. Its off by 50.

kenw232 · 12-19-2018, 02:08 AM

I noticed mysql starts to SUM incorrectly at row 62184. Summing my ID column up to 62183 reports both the same SUM'd number in Excel and MySQL. Yet as soon as go up one more row I get this, its off by 3.

Excel =SUM(A1:A62184): 5231630907980670

MySQL:
mysql> select format(sum(ID), '000000000000000') as auto_id_total from Table where row_num <= 62184;
+-----------------------+
| auto_id_total |
+-----------------------+
| 5,231,630,907,980,673 |
+-----------------------+
1 row in set (0.00 sec)

Why?

l0f4r0 · 12-19-2018, 03:15 AM

What is the output for the following?

Code:

SELECT ID from Table LIMIT 62183,3

michaelk · 12-19-2018, 10:50 AM

I think you have discovered the limitations of precision, significant figures, round off, truncation, and binary storage. Excel is limited to 15 significant digits and those numbers displayed greater then 15 are truncated. It isn't mysql that is wrong.

https://en.wikipedia.org/wiki/Numeri...icrosoft_Excel

mysql also has significant digit limitations depending on data type.

kenw232 · 12-19-2018, 11:40 AM

Quote:

Originally Posted by l0f4r0

What is the output for the following?

Code:

SELECT ID from Table LIMIT 62183,3

I get this.

Code:

mysql> SELECT ID from Table LIMIT 62183,3;     
+--------------+
| ID           |
+--------------+
| 170318068013 |
| 170318068014 |
| 170318068021 |
+--------------+
3 rows in set (2.58 sec)

The numbers are exact and correct when compared to the excel columns. I guess I have found a limit of some sort.

l0f4r0 · 12-19-2018, 11:54 AM

What is you ID column type?
As you definitely has a very huge SUM (because your IDs are very very big), I'm not sure it's a good idea to get rid of the scientific notation (i.e. exponential value)

kenw232 · 12-19-2018, 05:37 PM

Quote:

Originally Posted by l0f4r0

What is you ID column type?
As you definitely has a very huge SUM (because your IDs are very very big), I'm not sure it's a good idea to get rid of the scientific notation (i.e. exponential value)

ID is varchar (20).

michaelk · 12-19-2018, 06:44 PM

I have not played with mysql/mariadb much so I am surprised that you can sum a character string without a cast function.

You have discovered an Excel limitation... I do not know what version of Excel you are running but check this out:
https://support.microsoft.com/en-us/...mbers-in-cells

I'm not sure what you are trying to accomplish by adding the IDs.

l0f4r0 · 12-19-2018, 07:18 PM

I guess your IDs are >=0, not signed (no + or - sign) and with no decimals.
Please run the following instruction and tell us its output:

Code:

SELECT SUM(CAST(ID AS UNSIGNED)) from Table;

kenw232 · 12-19-2018, 10:54 PM

This is what I got.

Code:

mysql> SELECT SUM(CAST(ID AS UNSIGNED)) from Table;
+---------------------------+
| SUM(CAST(ID AS UNSIGNED)) |
+---------------------------+
|         61050470996456889 |
+---------------------------+
1 row in set (8.99 sec)

Its even different again. Before I got 61050470996395050. Which is different then excel as was my concern above.

astrogeek · 12-19-2018, 11:59 PM

Quote:

Originally Posted by kenw232

Its even different again. Before I got 61050470996395050. Which is different then excel as was my concern above.

Have you read the posts above by michaelk? That really looks to be the definitive explanation.

To be very clear, it is almost certainly excel that is producing an "incorrect"* result, making the comparison to excel meaningless.

I too am curious what is the purpose of summing the IDs? What is the meaning of the ID column?

* "incorrect" meaning limited by 15 significant digit precision

astrogeek · 12-20-2018, 12:52 AM

Quote:

Originally Posted by kenw232

I get this.

Code:

mysql> SELECT ID from Table LIMIT 62183,3;     
+--------------+
| ID           |
+--------------+
| 170318068013 |
| 170318068014 |
| 170318068021 |
+--------------+
3 rows in set (2.58 sec)

The numbers are exact and correct when compared to the excel columns. I guess I have found a limit of some sort.

In addition to the excel precision problem, you may indeed be hitting the limit of 64-bit integers which is the UNSIGNED type in MySQL. That is something over 18 followed by 18 zeros, a twenty digit number at most.

In the above selection you show 3 twelve digit numbers beginning with 17, so adding six of those values in that range will carry to the next power of 10 - a thirteen digit number... do that ten times (~60 IDs in that range) and you have a fourteen digit number, ten times that (~600 in that range) and you have a 15 digit number, 6000 to get to 16 digits, 60000 to 17 digits... and that is a tiny slice of that very specific number range!

Taken together with the fact that your SUM query took 8.99 seconds, even assuming reasonably recent hardware, indicates likely very many times that number of values... you may very likely be in danger of exceeding the 64 bit integer limit. I do not know if MySQL rolls over zero, returns an error or simply preduces meaningless results in that case.

Plus, you are not actually storing the data as integer types, so they must be cast to integers which might impose yet another limiting factor.

Again, I am curious to know what is the meaning of those IDs that would make their sum useful?