1

If I have a CLOB field that contains multiple values separated by commas, and need to total them to get a final output, how can I achieve that in SQL Developer?

Example table:

STOCK | COST

ABCDE | 258.40,299.50
FGHIJ | 100.50,70.50,95.30

I would like to be able to select the total for each row.

For ABCDE looking to select a total of 557.90

For FGHIJ looking to select a total of 266.30

jarlh
  • 42,561
  • 8
  • 45
  • 63
KJ33
  • 19
  • 2
  • If you know `SUM(cost) .. GROUP BY` ... Just use this one Possible duplicate of [SQL Server split CSV into multiple rows](http://stackoverflow.com/questions/9811161/sql-server-split-csv-into-multiple-rows) – Juan Carlos Oropeza Jul 06 '16 at 14:42
  • 3
    Storing values as CSV is very bad db design. You should change it – Jens Jul 06 '16 at 14:43
  • @JuanCarlosOropeza Do you think the solution works also in Oracle DB? – Jens Jul 06 '16 at 14:44
  • 2
    I agree with Jens. Do not store comma separated values in a single column. Read up on database normalization –  Jul 06 '16 at 14:45
  • @Jens Sorry, I was working on sql server at the moment and got confused. Oracle is easier ... http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle – Juan Carlos Oropeza Jul 06 '16 at 14:48

2 Answers2

0

If you have Oracle 12, you can use LATERAL:

select t.stock, sum(to_number(p.cst,'9999999.9999')) total
 from table_name t,
 lateral (select regexp_substr(t.cost,'[^,]+', 1, level) cst from dual
          connect by regexp_substr(t.cost, '[^,]+', 1, level) is not null) p
group by t.stock

Otherwise:

select stock, sum(cst) total
 from (
        select stock,to_number(column_value,'9999999.9999') cst
       from  table_name t, xmltable(('"'|| REPLACE(t.cost, ',', '","')|| '"'))        
      ) p
group by stock
Mottor
  • 1,938
  • 3
  • 12
  • 29
0

Here's a way using a CTE (Common Table Expression) with a regex that handles NULL list elements (or explicitly ignore them in the query, SUM ignores them at any rate):

SQL> -- First build the base table.
SQL> with tbl(stk, cst) as (
     select 'ABCDE', ',258.40,299.50'       from dual union
     select 'FGHIJ', '100.50,70.50,,,95.30' from dual
   ),
   -- Turn the list into a table using the comma as the delimiter. Think of it
   -- like a temp table in memory. This regex format handles NULL list elements.
   example_tbl(stock, cost) as (
     select stk, regexp_substr(cst, '(.*?)(,|$)', 1, level, NULL, 1)
     from tbl
     connect by regexp_substr(cst, '(.*?)(,|$)', 1, level) is not null
     group by stk, level, cst
   )
   -- select * from example_tbl;
   SELECT stock, to_char(sum(cost), '9990.99') Total
   from example_tbl
   group by stock;

STOCK TOTAL
----- --------
ABCDE   557.90
FGHIJ   266.30

SQL>
Gary_W
  • 9,933
  • 1
  • 22
  • 40