This discussion is archived
13 Replies Latest reply: May 16, 2012 5:52 AM by 846290

# Sum Calculation

Currently Being Moderated
Hi,

I am trying to sum up some rows, please see sample data:

create table test
(ID NUMBER,
SEQ NUMBER,
COST NUMBER);

insert into test values ( 001, 1, 123);
insert into test values (002, 2, 45);
insert into test values ( 003, 3, 81);
insert into test values (004, 4, 39);

select id, seq, cost/sum(cost)
from test
group by id, seq

I would like each row to show the cost divided by total cost of all 4 columns

e.g. 123/288
45/288
81/288 etc.

Can anyone help with this calculation?

Thanks
• ###### 1. Re: Sum Calculation
Currently Being Moderated
.

Edited by: BrunoSales on 15/05/2012 09:26
• ###### 2. Re: Sum Calculation
Currently Being Moderated
Use analytic sum:
``````SQL> select id, seq, cost/sum(cost) over()
2  from test
3  /

ID        SEQ COST/SUM(COST)OVER()
---------- ---------- --------------------
1          1           .427083333
2          2               .15625
3          3               .28125
4          4           .135416667

SQL> ``````
SY.
• ###### 3. Re: Sum Calculation
Currently Being Moderated
Hi,

That's what RATIO_TO_REPORT was designed for:
``````select    id
,        seq
,       cost
,       RATIO_TO_REPORT (cost) OVER ()     AS r
from        test
;``````
Output:
```````       ID        SEQ       COST          R
---------- ---------- ---------- ----------
1          1        123 .427083333
2          2         45     .15625
3          3         81     .28125
4          4         39 .135416667``````
You don't have to display cost, of course. I did, just to make the output easier to understand.

It works with GROUP BY, too:
``````select    id
,        seq
,       SUM (cost)                    AS total_cost
,       RATIO_TO_REPORT (SUM (cost)) OVER ()     AS r
from        test
group by  id
,            seq
;``````
• ###### 4. Re: Sum Calculation
Currently Being Moderated
Just in case you have more than one row for same id and seq:
``````SELECT id, seq, SUM(cost) total , SUM(cost) OVER () grand_total, RATIO_TO_REPORT(sum(cost)) OVER () ratio
FROM test
GROUP BY id, seq;``````
Regards,
Ankit Rathi
http://theoraclelog.blogspot.in/
• ###### 5. Re: Sum Calculation
Currently Being Moderated
There are some saddle differences between taking a ratio and using ration_to_report:
``````SQL> update test set cost = 0;

4 rows updated.

SQL> select id, seq, cost/sum(cost) over()
2  from test
3  /
select id, seq, cost/sum(cost) over()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select    id
2  ,    seq
3  ,   cost
4  ,   RATIO_TO_REPORT (cost) OVER () AS r
5  from    test
6  ;

ID        SEQ       COST          R
---------- ---------- ---------- ----------
1          1          0
2          2          0
3          3          0
4          4          0

SQL> ``````
SY.
• ###### 6. Re: Sum Calculation
Currently Being Moderated
Hi Frank (and Solomon),

I ran your SQL and it works fine. The only problem I have is that the answer displays
to about 40 digits of precision. I then defined cost as NUMBER(5,2) in the hope that
this would give an answer in that form - it doesn't work - still have huge number of digits.

How would I get an answer in the form NUMBER(5,2)?

TIA,

Paul...
• ###### 7. Re: Sum Calculation
Currently Being Moderated
Paulie wrote:

How would I get an answer in the form NUMBER(5,2)?
``````SQL> select  id,
2          seq,
3          COST/SUM(COST)OVER() ratio,
4          cast(COST/SUM(COST)OVER() as number(5,2)) ratio_52
5    from  test
6  /

ID        SEQ      RATIO   RATIO_52
---------- ---------- ---------- ----------
1          1 .427083333        .43
2          2     .15625        .16
3          3     .28125        .28
4          4 .135416667        .14

SQL> select  id,
2          seq,
3          COST/SUM(COST)OVER() ratio,
4          trunc(COST/SUM(COST)OVER(),2) ratio_trunc,
5          round(COST/SUM(COST)OVER(),2) ratio_round
6    from  test
7  /

ID        SEQ      RATIO RATIO_TRUNC RATIO_ROUND
---------- ---------- ---------- ----------- -----------
1          1 .427083333         .42         .43
2          2     .15625         .15         .16
3          3     .28125         .28         .28
4          4 .135416667         .13         .14

SQL>  ``````
SY.

Edited by: Solomon Yakobson on May 15, 2012 12:53 PM
• ###### 8. Re: Sum Calculation
Currently Being Moderated
Hi,
Paulie wrote:
How would I get an answer in the form NUMBER(5,2)?
Normally, I let the front end handle display issues like that.
In SQL*Plus, I would say
``COLUMN  r       FORMAT 999.99``
before running the query.
• ###### 9. Re: Sum Calculation
Currently Being Moderated
>
SQL> select id,
2 seq,
3 COST/SUM(COST)OVER() ratio,
4 trunc(COST/SUM(COST)OVER(),2) ratio_trunc,
5 round(COST/SUM(COST)OVER(),2) ratio_round
6 from test
7 /

ID SEQ RATIO RATIO_TRUNC RATIO_ROUND
---------- ---------- ---------- ----------- -----------
1 1 .427083333 .42 .43
Thanks Solomon,

As usual, an explanation above and beyond...

Paul...

SY.
• ###### 10. Re: Sum Calculation
Currently Being Moderated
Hi there,

I am having some problems with negative numbers as I would like to excude those in the sum calculation e.g

TRUNCATE TABLE TEST;

insert into test values (001, 1, 4568);
insert into test values (002, 2, -23675);
insert into test values (003, 3, 989);
insert into test values (004, 4, -678);

I would like to ignore the negative values in the calculation e.g.

select id, seq, cost, case when cost > 0 then cost end /case when cost > 0 then sum(cost) end total
from test
group by id, seq, cost

The above does not work, I would like to only sum the positive cost values. In the above example the negative values are also being summed up

can anyone help, almost there

Thanks
• ###### 11. Re: Sum Calculation
Currently Being Moderated
may be you are looking this
``````select id, seq, cost, case when cost > 0 then cost end /case when cost > 0 then sum(cost) end total
from test
group by id, seq, cost
having cost not like '-%';``````
• ###### 12. Re: Sum Calculation
Currently Being Moderated
``````
.....
having cost not like '-%';
``````
Please don't ever do anything like that again ... cost is numeric so use cost > 0
• ###### 13. Re: Sum Calculation
Currently Being Moderated
Hi,

If you want that the denominator sum should only include positive values then below query willhelp.
``````SQL>
SQL> WITH test AS (
2     SELECT '001' id, 1 seq, 4568 cost FROM dual UNION
3     SELECT '002' id, 2 seq, -23675 cost FROM dual UNION
4     SELECT '003' id, 3 seq, 989 cost FROM dual UNION
5     SELECT '004' id, 4 seq, -678 cost FROM DUAL
6     )
7  SELECT id, seq, cost,
8         CAST(cost / SUM(CASE WHEN cost > 0 THEN cost ELSE 0 END) OVER() AS NUMBER(5,2)) Net
9    FROM test
10  /

ID         SEQ       COST     NET
--- ---------- ---------- -------
001          1       4568    0.82
002          2     -23675   -4.26
003          3        989    0.18
004          4       -678   -0.12

SQL> ``````

#### Legend

• Correct Answers - 10 points