Search code examples
mysqlsum

MySQL - what does SUM(string value) actually do?

I'm trying to figure out how MySQL treats strings in a SUM() function. Granted, this likely won't happen on purpose, but I've had students ask me what MySQL is doing and why it's providing a valid answer (although it may not make any sense) instead of an error.

For example, here is some sample data:

 DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (my_string VARCHAR(20));

INSERT INTO my_table VALUES
('1 thing'),
('2 things'),
('three things'),
('4 things'),
('five things'),
('six things');

The query

SELECT SUM(my_string) FROM my_table;

yields

+----------------+
| SUM(my_string) |
+----------------+
|              7 |
+----------------+

I can't find anything in the reference manual that discusses this.

Anybody have an idea of why this query produces these results and not an error?

Thanks in advance!

Solution

  • This question could be considered a duplicate of mysql SUM of VARCHAR fields without using CAST

    Specifically, the string '10% Happier' is being translated to 10, and every other string to 0, so the sum() or total is 10.

    EDIT to follow the question's edit:

    ('1 thing'), becomes 1
('2 things'), becomes 2
('three things'), becomes 0
('4 things'), becomes 4
('five things'), becomes 0
('six things'); becomes 0

    1+2+0+4+0+0 = 7.