ranit@XE11GR2>> select SUM(x) from(
2 select substr(568,level,1) x from dual
3 connect by level <= length(568)
4 );
SUM(X)
----------
19
with data as (
select 24223 n from dual
)
select
sum(to_number(substr(abs(n), level, 1))) s
from data
connect by
level <= length(n)
S
"13"
Edited by: chris227 on 08.03.2013 06:39 1 with data as (
2 select 123 n from dual
3 union all
4 select null from dual
5 )
6 select
7 nvl(to_number(substr(n,1,1)),0)
8 +nvl(to_number(substr(n,2,1)),0)
9 +nvl(to_number(substr(n,3,1)),0)
10 +nvl(to_number(substr(n,4,1)),0)
11 s
12* from data
SQL> /
S
----------
6
0
Do you want touse it in a view or update a column? 1 with data as (
2 select 123 n from dual
3 union all
4 select 456 from dual
5 union all
6 select null from dual
7 )
8 select
9 sum(
10 nvl(to_number(substr(n,1,1)),0)
11 +nvl(to_number(substr(n,2,1)),0)
12 +nvl(to_number(substr(n,3,1)),0)
13 +nvl(to_number(substr(n,4,1)),0)
14 )
15 s
16* from data
SQL> /
S
----------
21
also
select
sum(
nvl(floor(abs(n)/1000),0)
+nvl(floor(mod(abs(n)/100, 10)),0)
+nvl(floor(mod(abs(n)/10, 10)),0)
+nvl(mod(abs(n),10),0)
)
from
data
If you want also do a group by you should in fact create a user defined aggregate function.user447000 wrote:
Now I want to add up the "cross sums". So expect a result of (1+1+1)+ (2+2+2) = 9.
select sum(xmlcast(xmlquery(regexp_replace('0' || documentnumber,'(\d)','+\1') returning content) as number)) sum_of_digits
from foo_invoice
/
SUM_OF_DIGITS
-------------
9
SQL>
SY. Solomon Yakobson wrote:Solomon,select sum(xmlcast(xmlquery(regexp_replace('0' || documentnumber,'(\d)','+\1') returning content) as number)) sum_of_digits from foo_invoice /
select sum(
xmlcast(
xmlquery(
'sum(for $i in 1 to string-length($n)
return xs:integer(substring($n,$i,1)))'
passing to_char(documentnumber) as "n"
returning content
) as number
)
)
from foo_invoice ;
odie_63 wrote:No matter what OP does, splitting 100 million numbers into digits and summing them up will not be fast. But you're right, OP should stay away from xml (and hierarchical queries too). I'd stick with chris227 solution using mod/floor.
I wouldn't try that one on 100 millions rows ;)
with data as (
select 123 n from dual
union all
select 22 from dual
union all
select 44 from dual
union all
select 123 from dual
)
select sum(row_total) col_total from
(select n,
(select sum(REGEXP_SUBSTR(n, '[[:digit:]]', 1, level)) from dual connect by level <= length(n)) row_total
from data);
OR
with data as (
select 123 n from dual
union all
select 22 from dual
union all
select 44 from dual
union all
select 123 from dual
)
select sum(row_total) col_total from
(select n,
(select sum(nvl(to_number(substr(n,level,1)),0)) from dual connect by level <= length(n)) row_total
from data);
COL_TOTAL
----------
24
Cheers