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