How to GROUP BY a string part in MySQL

Question

EDIT:

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
-- WHERE
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 1

If the pattern is same you can do something as

select 
substring_index(string,'::',1) as string_val,
sum(number) as number
from mytable
group by string_val

Answer 2

you can do it with SUBSTRING_INDEX() like this:

SELECT string, COUNT(*)
FROM tbl
-- WHERE
GROUP BY SUBSTRING_INDEX(string, '::', 1);