CODEWITHSUNDEEP
mysqlstringgroup-by
E. Coil
E. Coil

Reputation: 77

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

Upvotes: 7

Views: 5048

Answers (5)

Priyanshu
Priyanshu

Reputation: 881

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

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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

Upvotes: 6

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

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);

Upvotes: 0

Alvin SIU
Alvin SIU

Reputation: 1042

Please try the following:

select substr(string,1,1)
     , count(*)
  from tbl
 group by 1
     ;

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can do it with SUBSTRING_INDEX() like this:

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

Upvotes: 1

Related Questions