E. Coil E. Coil - 7 months ago 8
SQL Question

How to GROUP BY a string part in MySQL

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

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