11 Replies Latest reply on Apr 25, 2012 8:30 AM by chris227

# see a subtotal line by line

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

`````` 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
Please take care of the valuable hint from Kim Berg Hansen on the default behaviour of the window clause of the analytical function.