Count distinct values separated by a comma

Question

I have this table called test

id	my_list
1	aa//11, aa//34, ab//65
2	bb//43, bb//43, be//54
3
4	cc//76

I want to count the distinct values in my_list, where each item in the list is separated by a comma. In this case:

• id=1 will have 3 distinct values
• id=2 will have 2 distinct values as bb//43 as shown up twice, thus 2 distinct values
• id=3 will have 0 distinct values as it as an empty list
• id=4 will have 1 since there is only 1 item in the list

I want to do this in pure SQL and not using a custom made procedure. I tried with the statement below but it is showing 1.

SELECT id, COUNT(DISTINCT my_list) as my_count
FROM test;

Expected result:

id	my_count
1	3
2	2
3	0
4	1

Answer 1

You need to turn your list into table to count distinct inside it. With json_table, for example.

with a(id, my_list) as (
  select 1, 'aa//11, aa//34, ab//65' from dual union all
  select 2, 'bb//43, bb//43, be//54' from dual union all
  select 3, null from dual union all
  select 4, 'cc//76' from dual
)
select
  id
  , (
      select count(distinct val)
      from json_table(
        /*Replace comma with quotes and comma: ','
          And wrap with array brackets
        */
        '[''' || regexp_replace(my_list, '\s*,\s*', ''',''') || ''']'
        , '$[*]'
        columns ( val varchar(20) path '$')
      )
    ) as cnt
from a

ID | CNT
-: | --:
 1 |   3
 2 |   2
 3 |   0
 4 |   1

db<>fiddle here