13 Replies Latest reply: May 16, 2012 7:52 AM by 846290 RSS

    Sum Calculation

    sliderrules
      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
          878451
          .

          Edited by: BrunoSales on 15/05/2012 09:26
          • 2. Re: Sum Calculation
            Solomon Yakobson
            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
              Frank Kulash
              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
                696547
                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
                  Solomon Yakobson
                  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
                    Paulie
                    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
                      Solomon Yakobson
                      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
                        Frank Kulash
                        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
                          Paulie
                          >
                          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
                            sliderrules
                            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
                              Venkadesh Raja
                              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
                                Dave Rabone
                                  
                                .....  
                                having cost not like '-%';
                                 
                                Please don't ever do anything like that again ... cost is numeric so use cost > 0
                                • 13. Re: Sum Calculation
                                  846290
                                  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>