0

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.

  • 1
    Having a comma separated list in tables is always an antipattern to be avoided. hint however for part of it (counting commas as an indication of number of elements). – danblack May 29 at 1:21
1
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.