11 Replies Latest reply: Apr 25, 2012 3:30 AM by chris227 RSS

    see a subtotal line by line

    claudioaragao
      Hi,people.

      I have the situation,

      line 1 = 10
      line 2 = 20
      line 3 = 30

      I need one select that show to me the subtotal in other column....like this

      colun 1 colun 2
      10 10
      20 30
      30 60
      .
      .
      .

      this is possible...?
      Thanks.
        • 1. Re: see a subtotal line by line
          Paul  Horth
          Have a look at "analytic functions".

          As an example:
          with table1 as (
          select 1 id, 10 col1 from dual
          union
          select 2, 20 from dual
          union
          select 3, 30 from dual
          )
          select sum(col1) over (order by id range unbounded preceding)  running_total
          from table1;
          
          RUNNING_TOTAL
          10
          30
          60
          Note I put an id in so I could order by it: in your example there does not appear to be a natural ordering. You will
          have to provide the ordering criteria.
          • 2. Re: see a subtotal line by line
            chris227
            with t as (
              select level*10 le from dual
              connect by level <= 10
            )
            
            select
              le
             ,sum(le) over (order by le) subtotal
            from t
            
            LE     SUBTOTAL
            10     10
            20     30
            30     60
            40     100
            50     150
            60     210
            70     280
            80     360
            90     450
            100     550
            • 3. Re: see a subtotal line by line
              chris227
              ordering by col1 is possible, isnt it?

              docs: "If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
              • 4. Re: see a subtotal line by line
                Paul  Horth
                ordering by col1 is possible, isnt it?

                Yes, but that may not be what is wanted.

                docs: "If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"

                True, just knocked it up off the top of my head and didn't check with docs :-)
                • 5. Re: see a subtotal line by line
                  Kim Berg Hansen
                  But beware of the difference between RANGE BETWEEN and ROWS BETWEEN:
                    1  select empno
                    2       , sal
                    3       , sum(sal) over (
                    4            order by sal
                    5            range between unbounded preceding and current row
                    6         ) sal_range
                    7       , sum(sal) over (
                    8            order by sal
                    9            rows between unbounded preceding and current row
                   10         ) sal_rows
                   11    from scott.emp
                   12*  where deptno=30
                  
                       EMPNO        SAL  SAL_RANGE   SAL_ROWS
                  ---------- ---------- ---------- ----------
                        7900        950        950        950
                        7521       1250       3450       2200
                        7654       1250       3450       3450
                        7844       1500       4950       4950
                        7499       1600       6550       6550
                        7698       2850       9400       9400
                  RANGE gives 3450 both for emp 7521 and 7654, because RANGE for both those records say "sum of all those with SAL less than or equal to 1250", which will be 950+1250+1250.

                  ROWS gives what we probably think we want :-)

                  And since the default is RANGE, I personally rarely use the default but explicitly write ROWS or RANGE depending on what I want. Otherwise I most likely would forget the possible "trap" when the default uses RANGE ;-)
                  • 6. Re: see a subtotal line by line
                    Paul  Horth
                    True, I was thinking along the lines of a sequential id for the order. But, yes, if you want
                    to use the sal itself: rows is the way to go!
                    • 7. Re: see a subtotal line by line
                      Kim Berg Hansen
                      I am also thinking both the principles and possibly a little bit of performance?

                      The default RANGE clause defines the "window" of data by the VALUE of the column in the ORDER BY.
                      ROW clause defines the "window" solely by the ordering.

                      I havent' benchmarked it but my logic tells me that windowing data by their value might imply slightly more work comparing values - and as the example with salary shows, the window actually kind of retrieves past the current row when there is a tie (well it doesn't really, but it can't simply just walk the data in order and add them to the subtotal one at a time, it has to "look forward" somehow inside the algorithm used.)
                      Windowing by row order could probably be a bit more efficient working simply on some internal array indices or something, allowing it to add each row to the subtotal as it traverses them.

                      Anyway, I may be wrong. And presumably if the column in the order by is a primary key or unique, then Oracle may very well be smart enough to know that then RANGE and ROWS is equivalent.

                      But I would still argue the principle that unless you specifically wish to define a window based on the value of the data, then it is IMHO a "best practice" to explicitly use ROWS even when RANGE gives the same result ;-)
                      • 8. Re: see a subtotal line by line
                        Paul  Horth
                        Well, I can see your reasoning, though the explain plans look the same.

                        Anyway, I would agree with you though I don't know why Oracle made range the default then.
                        • 9. Re: see a subtotal line by line
                          Kim Berg Hansen
                          Paul Horth wrote:
                          I don't know why Oracle made range the default then.
                          Exactly my point - IMHO it should have been ROWS that was the default ;-)
                          At least in all the analytic function statements I have written (and they are many...) I have used ROWS BETWEEN in 99% of the cases and only rarely had a case where RANGE BETWEEN was called for.
                          • 10. Re: see a subtotal line by line
                            chris227
                            Kim Berg Hansen wrote:
                            But beware of the difference between RANGE BETWEEN and ROWS BETWEEN:
                            And since the default is RANGE, I personally rarely use the default but explicitly write ROWS or RANGE depending on what I want. Otherwise I most likely would forget the possible "trap" when the default uses RANGE ;-)
                            Thanks for that valuable hint.
                            That's what i recommend and always try to too ... apart from that "rare" occasions i forget it ;-), hence i would consider my solution as not correct.

                            So i completed your example
                             select empno
                                  , sal
                             , sum(sal) over (
                             order by sal
                                        range between unbounded preceding and current row
                                     ) sal_range
                                   , sum(sal) over (
                                        order by sal
                                        rows between unbounded preceding and current row
                                     ) sal_rows
                                   , sum(sal) over (
                                        order by sal
                                        
                                     ) sal_default
                                from emp
                              where deptno=30
                            
                            EMPNO     SAL     SAL_RANGE     SAL_ROWS     SAL_DEFAULT
                            7900     950     950     950     950
                            7521     1250     3450     2200     3450
                            7654     1250     3450     3450     3450
                            7844     1500     4950     4950     4950
                            7499     1600     6550     6550     6550
                            7698     2850     9400     9400     9400
                            10 Points for you
                            • 11. Re: see a subtotal line by line
                              chris227
                              Please take care of the valuable hint from Kim Berg Hansen on the default behaviour of the window clause of the analytical function.