9 Replies Latest reply: Mar 11, 2013 5:17 AM by dilipkumar10285

# function for cross sum of numeric value

Hi,

I want to calculate the cross sum of a numeric value.
Example: "cross sum" of 567 = 5+6+7 = 18

I need something like:
SELECT SUM(cross_sum(document_number)) FROM invoice;

Is there any solution out of the box (Oracle 11g) or must I write my own function?

Matt
• ###### 1. Re: function for cross sum of numeric value
``````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
Dont know any, however
``````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
extended for negativ numbers
• ###### 3. Re: function for cross sum of numeric value
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
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
``````  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?

Ok, i see you changed your requirements.
Now you want the digit sum over all rows
``````  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.
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
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.
• ###### 6. Re: function for cross sum of numeric value
Solomon Yakobson wrote:
``````select  sum(xmlcast(xmlquery(regexp_replace('0' || documentnumber,'(\d)','+\1') returning content) as number)) sum_of_digits
from  foo_invoice
/``````
Solomon,

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 string-length(\$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
odie_63 wrote:
I wouldn't try that one on 100 millions rows ;)
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.

SY.
• ###### 8. Re: function for cross sum of numeric value
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
Hi,

``````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
Dilipkumar

Edited by: dilipkumar vishwakarma on Mar 11, 2013 3:47 PM