Re: newbie problem with analytic function

I think order by clause dont cause for cummulative sum or running total,whenever the
order_by_clause results in identical values for multiple rows, the function returns the
same result for each of those rows.

Khurram

fitzjarrell_at_cox.net wrote:
> ravi.superiors_at_gmail.com wrote:
> > Mark D Powell wrote:
> > > In general the order by within the partition exists to order the data
> > > for the purpose of calculationg the partition results. In your example
> > > the use of the order by results in the sum displayed being the
> > > cumulative sum of the salary within a department. In the second
> > > example where no order by clause exists within the partition by clause
> > > the displayed sum for all rows within a department is the department
> > > total.
> > >
> > > You should always include the Oracle edition and version in a post
> > > since the response may need to vary depending on this.
> > >
> > > HTH -- Mark D Powell --
> >
> >
> > thanx mark for yours response but a little confusion it is not written
> > anywhere in oracle
> > doc for analytic function that what you are syaing ,oracles doc did not
> > mention to force
> > order by clause for cummulative claculation for correct result.Still
> > its paining whats the
> > role of order by clause??
> >
> > sorry,i am using
> >
> > Oracle9i Enterprise Edition Release 9.2.0.1.0
> >
> > ravi
>
> Mark has already explained the role of the order by clause in this
> query: to produce a running total, rather than a departmental total.
> To use your example:
>
> select deptno,
> sal,
> sum(sal) over (partition by deptno order by empno) tot_dept_sal
> from emp;
>
> produces the following result set:
>
> DEPTNO SAL TOT_DEPT_SAL
> ---------- ---------- ------------
> 10 2450 2450
> 10 5000 7450
> 10 1300 8750 <=== salary total for
> dept 10
> 20 800 800
> 20 2975 3775
> 20 3000 6775
> 20 1100 7875
> 20 3000 10875 <=== salary total for
> dept 20
> 30 1600 1600
> 30 1250 2850
> 30 1250 4100
>
> DEPTNO SAL TOT_DEPT_SAL
> ---------- ---------- ------------
> 30 2850 6950
> 30 1500 8450
> 30 950 9400 <=== salary total for
> dept 30
>
> 14 rows selected.
>
>
> Now let's look at the query without the order by --
>
> select deptno,
> sal,
> sum(sal) over (partition by deptno) tot_dept_sal
> from emp
>
> which produces
>
> DEPTNO SAL TOT_DEPT_SAL
> ---------- ---------- ------------
> 10 2450 8750
> 10 5000 8750
> 10 1300 8750
> 20 2975 10875
> 20 3000 10875
> 20 1100 10875
> 20 800 10875
> 20 3000 10875
> 30 1250 9400
> 30 1500 9400
> 30 1600 9400
>
> DEPTNO SAL TOT_DEPT_SAL
> ---------- ---------- ------------
> 30 950 9400
> 30 2850 9400
> 30 1250 9400
>
> 14 rows selected.
>
> All you now return is the departmental total, not the running total
> you wanted. The result isn't wrong, it simply isn't what you expected.
> The construct
>
> (partition by deptno order by empno)
>
> appears to behave as though you'd written
>
> order by deptno, empno
>
> as the last line of your query; this is likely due to the grouping the
> sum() over (condition) is implementing to calculate the results. To
> ensure this order it would probably be better to write:
>
> select deptno,
> sal,
> sum(sal) over (partition by deptno order by empno) tot_dept_sal
> from emp
> order by deptno, empno;
>
> to ensure there is no question as to the order of the result set. This
> would appear to be 'overkill', however if the behaviour of the sum()
> over (condition) functionality changes in a subsequent release you
> would be fairly certain of returning a result set ordered as you want
> it; unless you tell Oracle how to order your data you can't guarantee
> how it will be returned.
>
> Again, Mark already explained what the order by is doing; I'm simply
> trying to restate it in hopes you'll understand.
>
>
> David Fitzjarrell
Received on Thu Aug 17 2006 - 02:44:35 CDT