Counting values comma separated

Question

I have the below table:

A   V
-   -
A1  B, C
A2  C, 2W
A3  D
A4  10K, 10W, 2A

I would like to count the values in V and order them by occurrence. Expected output:

O  COUNT(*)
-  -------
W       12
K       10
A        2
C        2
B        1
D        1

Answer 1

You can use combination of REGEX_SUBSTR() and REGEXP_REPLACE() functions along with hierarchical query assuming all comma-seperated elements, whenever white spaces are trimmed, are of the form a positive integer + a letter such as

WITH t2 AS
(
 SELECT TRIM(REGEXP_SUBSTR(v,'[^,]+',1,level)) AS v
   FROM t 
CONNECT BY level <= REGEXP_COUNT(v,',')+1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR a = a
)
SELECT NVL(SUM(REGEXP_REPLACE(v,'[^[:digit:]]')),
           CASE WHEN COUNT(*)>1 THEN COUNT(*) END)||REGEXP_REPLACE(v,'[^[:alpha:]]') AS o
  FROM t2 
 GROUP BY REGEXP_REPLACE(v,'[^[:alpha:]]')    

Demo

Edit (depending on the last edit of the question) : just remove conctenation operator, and conditional for COUNT aggregation such as

SELECT REGEXP_REPLACE(v,'[^[:alpha:]]') AS o,
       NVL(SUM(REGEXP_REPLACE(v,'[^[:digit:]]')),COUNT(*)) AS count 
  FROM t2 
 GROUP BY REGEXP_REPLACE(v,'[^[:alpha:]]') 
 ORDER BY count DESC, o

Demo