CODEWITHSUNDEEP
mysql
user3242861
user3242861

Reputation: 1929

Mysql - Sum varchar field to decimal

I have a column total with varchar type and I want to select all records and sum the total column but it doesn't sum the decimal part.

I have this values in total column:

 -----
| 5:00|
| 2:30|
| 5:00|
 -----

My query is that:

SELECT sum(cast(total as decimal(10,2))) as tudo FROM table

The query returns me 12.00 but should returns 12.30.

How can I do that?

Thank you

Upvotes: 0

Views: 375

Answers (1)

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (x TIME NOT NULL);

INSERT INTO my_table VALUES('5:00:00'),('2:30:00'),('5:00:00');

SELECT * FROM my_table;
+----------+
| x        |
+----------+
| 05:00:00 |
| 02:30:00 |
| 05:00:00 |
+----------+

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(x)))n FROM my_table;
+----------+
| n        |
+----------+
| 12:30:00 |
+----------+

Upvotes: 2

Related Questions