SQL & PL/SQL

Hi

I have a a below query and out put also given below

Query:

select distinct cont_id,ct.TAG245 from contact_tag ct where ct.CONT_ID='AEGA-3R43UM'

OUTPUT

Cont_id TAG245
AEGA-3R43UM 060|02.00|060|02.00|060|02.00|060|02.00

In the above output for TAG245 we have two values first value is percentage and second value is frequency.

I need to make all frequency values sum.(ie 02.00+02.00+02.00+02.00)

Expected output

Plannedcall
6


********************

Step 1
First I am approaching the above output dividing the each pipe symbols into each row using the below query but it is not working it is taking long time.Can you advice me on this so that i can goto next step:2.


The query
SELECT regexp_substr (ct.TAG245, '[|,]+', 1, level) frequency
FROM contact_tag ct
--where ct.CONT_ID='AEGA-3R43UM'
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (ct.TAG245, '[|,]+')) + 1
order by 1;



Step: 2
Once i get the output I will sum the even rows because all evenrows has frequency values.



Thanks