How to count occurrence of a substring in a comma separated string in MySQL

Question

Let's say I have selected records like this

+----+-------------------------------------------------------------------------------------
| id | groupedStatus                                                                      |
+----+-------------------------------------------------------------------------------------
| 1 | received,accepted,discarded,discarded,accepted,discarded,accepted,received,received |
| 2 | accepted,discarded,received,received,received,received,received                     |          
+----+-------------------------------------------------------------------------------------

Along with the above records, I also want to get the occurrence of each substring in the groupedStatus string,

for example, in the first row, the occurrences are as follows:

received: 3 accepted: 3 discarded: 3

Original Table Schema

Table1

ID int

Table 2:

ID
Table1ID
Status enum('received','accepted','discarded')

I am selecting above records using the following query

select t1.id, group_concat(t2.status) as groupedStatus from Table1 t1 inner join Table2 t2 on t1.id=t2.table1ID group by t1.id

Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Jun 09 '20 at 07:48
I am not storing data as comma-separated-values, it is a result of `group_concat` — Amir Saleem, Jun 09 '20 at 07:49
@AmirSaleem: please show us your original data and query then. It will be much easier to what you want *before* string aggregation. — GMB, Jun 09 '20 at 07:50
@AmirSaleem, ask a new question! (With table data and expected result.) — jarlh, Jun 09 '20 at 08:13

score 1 · Accepted Answer · answered Jun 09 '20 at 08:22

1

You can do this with conditional aggregation, like so:

select 
    t1.id, 
    sum(t2.status = 'received')  as received,
    sum(t2.status = 'accepted')  as accepted,
    sum(t2.status = 'discarded') as discarded,
    group_concat(t2.status) as groupedStatus 
from table1 t1 
inner join table2 t2 on t1.id = t2.table1ID 
group by t1.id

answered Jun 09 '20 at 08:22

GMB

216,147
25
84
135

1

+1 for this, I was using this `(CHAR_LENGTH(group_concat(case when t2.status = "received" then t2.status else null end)) - CHAR_LENGTH(REPLACE(group_concat(case when t2.status = "received" then t2.status else null end), ',', '')) + 1) as receivedCount` but yours is much simpler – Amir Saleem Jun 09 '20 at 08:28

How to count occurrence of a substring in a comma separated string in MySQL

1 Answers1