CODEWITHSUNDEEP
sqloracle-database
Shalini
Shalini

Reputation: 1

Oracle SQL Query, function to split and count value in column?

I have a scenario where I would like to count how many times a value appears in a column but the problem now is that the column is having multiple delimited by a delimiter.

I tried using Split Function but not getting desired results Below is the sample how data would be:

ID   Column_Value
011  Analytics,Transpose  
012  Analytics  
013  Analytics,Transpose,Vector  
014  Vector,Dimension

Now, my requirement is to calculate number of times Analytics appear in the Column_Value , calculate number of times Transpose appear and finally count number of times rest of the other values (i.e. Vector and Dimnesion) appear.

Expected Result: 

Count_Analytics  Count_Transpose  Count_Rest
3                2                2

Upvotes: 0

Views: 705

Answers (2)

Chaitanya Kotha
Chaitanya Kotha

Reputation: 476

You can use the below code

    SELECT  DECODE(TEXT_RESULT, 'ANALYTICS', 'ANALYTICS', 'TRANSPOSE', 'TRANSPOSE', 'OTHERS') RES, COUNT(1)
  FROM (WITH TEMP AS (SELECT 1 ID, 'ANALYTICS,TRANSPOSE' TEXT FROM DUAL
                      UNION
                      SELECT 2 ID, 'ANALYTICS' TEXT FROM DUAL
                      UNION
                      SELECT 3 ID, 'ANALYTICS,TRANSPOSE,VECTOR' TEXT FROM DUAL
                      UNION
                      SELECT 4 ID, 'VECTOR,DIMENSION' TEXT FROM DUAL)
         SELECT DISTINCT ID,
                         REGEXP_SUBSTR(TEXT, '[^,]+', 1, LEVEL) TEXT_RESULT
           FROM TEMP
         CONNECT BY LEVEL <= REGEXP_COUNT(TEXT, '[^,]+'))
          GROUP BY DECODE(TEXT_RESULT, 'ANALYTICS', 'ANALYTICS', 'TRANSPOSE', 'TRANSPOSE', 'OTHERS');

And the result would be as below.

    RES         COUNT(1)
1   ANALYTICS   3
2   OTHERS      3
3   TRANSPOSE   2

Using regexp_substr, we are separating the text with coma as delimiter and then you can get the count.

If you still want the result as shown in your question, you can use pivot function..

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191415

If you want to count how many rows have any value other than Analytics or Transpose you can do:

select
  count(case when regexp_like(column_value, '(^|,)Analytics(,|$)') then column_value end)
    as count_analytics,
  count(case when regexp_like(column_value, '(^|,)Transpose(,|$)') then column_value end)
    as count_transpose,
  count(regexp_replace(regexp_replace(column_value, '(^|,)Analytics(,|$)'), '(^|,)Transpose(,|$)'))
    as count_rest
from your_table;

COUNT_ANALYTICS COUNT_TRANSPOSE COUNT_REST
--------------- --------------- ----------
              3               2          2

If you want to count the number of appearances of those other values, rather than the number of rows, you can do:

select
  count(case when regexp_like(column_value, '(^|,)Analytics(,|$)') then column_value end)
    as count_analytics,
  count(case when regexp_like(column_value, '(^|,)Transpose(,|$)') then column_value end)
    as count_transpose,
  sum(regexp_count(
    regexp_replace(regexp_replace(column_value, '(^|,)Analytics(,|$)'), '(^|,)Transpose(,|$)'),
     '[^,]+')) as count_rest
from your_table;

COUNT_ANALYTICS COUNT_TRANSPOSE COUNT_REST
--------------- --------------- ----------
              3               2          3

Upvotes: 1

Related Questions