Get array size for GROUP_CONCAT()

I am running a query where I used GROUP_CONCAT() and now I want to know the length of this array. I used LENGTH(), however, this shows the number of characters in the row. Should I even use GROUP_CONCAT() in the first place?

The results I am expecting:

Id	GROUP_CONACT()	LENGTH
1	A, B	2
2	C, D	2
3	E, F, G	3
4	A, D	2
5	A, B, D, E, G	5

CodePudding user response：

I think what you are trying to achieve is counting the number of occurrence of ',' 1: Use

ROUND (   
        (
          LENGTH(concated)-LENGTH(REPLACE(concated, ",", "")))/LENGTH(",") 1
    ) AS COUNT

Replace concated to your GROUP_CONCAT

CodePudding user response：

I don't see why you can't just use the COUNT() function, e.g.:

item | accessory
1    | a
1    | b
1    | c
2    | d
2    | e

SELECT item,
       GROUP_CONCAT(accessory) AS accessories,  -- the CSV string
       COUNT(*) AS num_accessories              -- the "size" of the array
FROM yourTable
GROUP BY item;