mysql aggregate function for varchar column

Question

I need to calculate the sum of one column(col2) , but the column has both numbers and text. How do I exclude the text alone before I use sum()? The table has around 1 million rows, so is there any way other than replacing the text first? My query will be :

Select col1,sum(col2) from t1 group by col1,col2

Thanks in advance

score 2 · Answer 1 · answered May 04 '15 at 07:02

2

You can use regexp to filter the column:

Select col1,sum(col2) from t1  WHERE col2 REGEXP '^[0-9]+$' group by col1,col2

answered May 04 '15 at 07:02

Jens

67,715
15
98
113

score 0 · Accepted Answer · answered May 04 '15 at 07:11

You could use MySQL built in REGEXP function.

to learn more visit : https://dev.mysql.com/doc/refman/5.1/en/regexp.html

Or another way is using CAST or CONVERT function

to learn in detail : https://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Hope this is helpful

score 0 · Answer 3 · answered May 04 '15 at 07:35

Assuming you mean the number is at the beginning of the tex, the easiest way is simply to use implicit conversion:

Select col1, sum(col2 + 0)
from t1
group by col1, col2;

If col2 starts with a non-numeric character, then MySQL will return 0. Otherwise, it will convert the leading numeric characters to a number.

Note that your query doesn't really make sense, because you are aggregating by col2 as well as including it in the group by. I suspect you really want:

Select col1, sum(col2 + 0)
from t1
group by col1;

mysql aggregate function for varchar column

3 Answers3