This content has been marked as final.
Show 9 replies

1. Re: function for cross sum of numeric value
ranit B Mar 8, 2013 8:41 AM (in response to user447000)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

2. Re: function for cross sum of numeric value
chris227 Mar 8, 2013 8:40 AM (in response to user447000)Dont know any, however
Edited by: chris227 on 08.03.2013 06:39with 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"
extended for negativ numbers 
3. Re: function for cross sum of numeric value
user447000 Mar 8, 2013 8:56 AM (in response to chris227)I'm not familiar with this "connect by".
Just to make my example more complete:
CREATE TABLE foo_invoice
(
documentnumber NUMBER(4)
);
INSERT INTO foo_invoice
(documentnumber ) VALUES (111);
INSERT INTO foo_invoice
(documentnumber ) VALUES (222);
Now I want to add up the "cross sums". So expect a result of (1+1+1)+ (2+2+2) = 9.
This is why I wrote in my first post:
"SELECT SUM(cross_sum(documentnumber)) FROM foo_invoice".
In my real table I have about 100 million records. So I nees a performant solution. 
4. Re: function for cross sum of numeric value
chris227 Mar 8, 2013 9:59 AM (in response to user447000)Ok, in this case connect by may be not the best approach.
As there are only at most 4 digits, did you try something like
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 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
What's your DBVersion?
Ok, i see you changed your requirements.
Now you want the digit sum over all rows
If you want also do a group by you should in fact create a user defined aggregate function.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
Blue shadow gave e plenty of example in this forum already for this.
Edited by: chris227 on 08.03.2013 07:32
Edited by: chris227 on 08.03.2013 07:57 
5. Re: function for cross sum of numeric value
Solomon Yakobson Mar 8, 2013 9:31 AM (in response to user447000)user447000 wrote:
Now I want to add up the "cross sums". So expect a result of (1+1+1)+ (2+2+2) = 9.
SY.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>

6. Re: function for cross sum of numeric value
odie_63 Mar 8, 2013 10:07 AM (in response to Solomon Yakobson)Solomon Yakobson wrote:
Solomon,select sum(xmlcast(xmlquery(regexp_replace('0'  documentnumber,'(\d)','+\1') returning content) as number)) sum_of_digits from foo_invoice /
I wouldn't try that one on 100 millions rows ;)
As far as XQuery is concerned, the following is more scalable (no functional evaluation involved) :select sum( xmlcast( xmlquery( 'sum(for $i in 1 to stringlength($n) return xs:integer(substring($n,$i,1)))' passing to_char(documentnumber) as "n" returning content ) as number ) ) from foo_invoice ;

7. Re: function for cross sum of numeric value
Solomon Yakobson Mar 8, 2013 11:36 AM (in response to odie_63)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 ;)
SY. 
8. Re: function for cross sum of numeric value
user447000 Mar 11, 2013 2:17 AM (in response to Solomon Yakobson)Hi,
after all I wrote a small helper function based on
...
PARALLEL_ENABLE
...
BEGIN
vLength := LENGTH(pRENr);
rc := 0;
FOR i IN 1..vLength LOOP
rc := rc + nvl(to_number(substr(pRENr,i,1)),0);
END LOOP;
RETURN rc;
END;
BTW pRENr is NUMBER(16).
Performance was quite good. For 66 millions RENRs
a
SELECT /*+ PARALLEL(r 32) */ SUM(getCrossSum( i.RENR))
FROM invoice i;
took 20 seconds which is acceptable. 
9. Re: function for cross sum of numeric value
dilipkumar10285 Mar 11, 2013 5:17 AM (in response to user447000)Hi,
Hope this will help you.
ORwith 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);
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);
CheersCOL_TOTAL  24
Dilipkumar
Edited by: dilipkumar vishwakarma on Mar 11, 2013 3:47 PM