Mysql – count and group_concat

countgroup-concatenationMySQL

I have data in a table like so:

+---------+----------------------------------------------------+
| videoid | ips                                                |
+---------+----------------------------------------------------+
|      22 | 192.168.1.1,192.168.1.2,192.168.1.3,192.168.1.5    |
|      22 | 192.168.1.10                                       |
|      22 | 192.168.1.1                                        |
|      3  | 192.168.1.4                                        |
+---------+----------------------------------------------------+
4 rows in set (0.00 sec)

I need to get the result like so, cnt is the unique count of ips for each videoid:

+---------+-------+
| videoid | cnt   |
+---------+-------+
|      22 | 5     |
|      3  | 1     |
+---------+-------+
2 row in set (0.52 sec)

thank you.

Best Answer

WITH RECURSIVE cte1 AS (
SELECT videoid, 
       SUBSTRING_INDEX(ips, ',', 1) ip, 
       SUBSTRING(ips FROM 1 + LOCATE(',', ips) FOR 255) slack
FROM test
UNION ALL
SELECT videoid, 
       SUBSTRING_INDEX(slack, ',', 1) ip, 
       SUBSTRING(slack FROM 1 + LOCATE(',', slack) FOR 255) slack
FROM cte1
WHERE LOCATE(',', slack)
),
cte2 AS (
SELECT videoid, 
       ip 
FROM cte1
UNION
SELECT videoid, 
       slack 
FROM cte1 
WHERE !LOCATE(',', slack)
)
SELECT videoid, COUNT(ip) cnt
FROM cte2
GROUP BY videoid
Related Question