How to count the length of column seperated by ',' after using group_concat

Question

I have a table looks like below:

ID                  path                      
| 1               YouTube,Newsletter,Social  
| 2               YouTube,Newsletter         
| 3               YouTube

Now I want to create a column to count the length of the path column. such as below:

ID                  path                      count    weights              
| 1               YouTube,Newsletter,Social     3        0.33
| 2               YouTube,Newsletter            2        0.5
| 3               YouTube                       1         1

How do I do this?

I have tried JSON_LENGTH but couldn't get the command working.

PS. essentially I'm trying to replicate a query in PostgreSQL: ' select user_id, channels, 1.0 / array_length(channels, 1) as weights from ( // ... query for marketing_channels as before)' I am using MYSQL.

select d.email_entry_id
      ,d.channels 
      ,JSON_LENGTH(d.channels) 
from (   
  select email_entry_id
       ,group_concat(attribution_string order by visit_date asc separator ',' ) as channels
  from database) d

error message: Error Code: 1370. execute command denied to user 'yb'@'%' for routine 'company.JSON_LENGTH'

Hope the question is clear enough. let me know if i need to clarify anything.

score 1 · Accepted Answer · answered Sep 09 '19 at 22:16

1

If I followed you correctly, you could simply extend the logic of your existing query (which, by the way, seems to be missing a GROUP BY clause). Instead of querying the aggregated data, it would be simpler to start from the original data, like:

SELECT
  email_entry_id,
  GROUP_CONCAT(attribution_string ORDER BY visit_date SEPARATOR ',' ) as channels,
  COUNT(*) as `count`,
  1/COUNT(*) as weight
FROM database
GROUP BY email_entry_id

answered Sep 09 '19 at 22:16

GMB

216,147
25
84
135

oh you re right. sweet. – Erica Shi Sep 09 '19 at 22:21
Can I ask a follow up question here? if I have Youtube, Youtube, Newsletter in Path column. Can I also count distinct channel here? So, instead 3, how do I get 2 in the result? thank you! @GMB – Erica Shi Sep 10 '19 at 16:42

score 1 · Answer 2 · answered Sep 10 '19 at 01:09

1

There is a very common trick to achieve such outcome, demonstrated by following query

SELECT ID, PATH, 
        (LENGTH(PATH) - LENGTH(REPLACE(PATH, ',', ''))) + 1 COUNT 
FROM DATABASE /* OR WHATEVER IS THE TABLE NAME */

The result

answered Sep 10 '19 at 01:09

Gro

1,613
1
13
19

thank you Gro. both solutions work – Erica Shi Sep 10 '19 at 15:57

How to count the length of column seperated by ',' after using group_concat

2 Answers2