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
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
substring_index(substring_index(string,'::',2),'::',-1)you can do it with SUBSTRING_INDEX() like this:
SELECT string, COUNT(*)
FROM tbl
-- WHERE
GROUP BY SUBSTRING_INDEX(string, '::', 1);
Please try the following:
select substr(string,1,1)
, count(*)
from tbl
group by 1
;
Just use substr for this:
SELECT substr(string,1, 1), COUNT(*)
FROM tbl
-- WHERE
GROUP BY substr(string,1, 1);
Or more sophisticated SUBSTRING_INDEX:
SELECT SUBSTRING_INDEX(string, '::', 1), COUNT(*)
FROM tbl
-- WHERE
GROUP BY SUBSTRING_INDEX(string, '::', 1);
select left(string,1),count(*) from table where string is not null group by left(string,1) order by left(string,1)
I hope this helps, LEFT(string) will only take left most character
LEFT(string,10)if the charactersstuff::?::in front is the standard format/length of values in your string column.RIGHT(string,3)is the vice versa :)