E. Coil E. Coil - 2 years ago 87
SQL Question

How to GROUP BY a string part in MySQL


Assuming you've got the following table:

id string number
1 stuff::a::312 5
2 stuff:::a::312 6
3 stuff::a::233 2
4 stuff:b::213 1
5 stuff::b::222 1
6 stuff::c 5

The following doesn't work of course:

SELECT string, COUNT(*)
FROM tbl
GROUP BY string;

The wished result:

string numbers
a 13
b 2
c 5

Sorry, but please note that after c is no :: but before, just like the rest

Answer Source

If the pattern is same you can do something as

substring_index(string,'::',1) as string_val,
sum(number) as number
from mytable
group by string_val
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download