Re: newbie problem with analytic function

From: <fitzjarrell_at_cox.net>
Date: 3 Aug 2006 09:30:08 -0700
Message-ID: <1154622608.937354.34140@i3g2000cwc.googlegroups.com>

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 03 2006 - 11:30:08 CDT