0 Replies Latest reply: Jan 24, 2013 3:23 AM by a_cute_person RSS

    help model clause

    a_cute_person
      I have a query like as below

      select empno,deptno, sum(sal) over (partition by deptno) tot_sal from scott.emp;


      will give result like this

      EMPNO DEPTNO TOT_SAL
      ---------- ---------- ----------
      7934 10 8750
      7782 10 8750
      7839 10 8750
      7902 20 10875
      7876 20 10875
      7566 20 10875
      7369 20 10875
      7788 20 10875

      8 rows selected.

      but as you can see the total sal and deptno repeats , what i want is dept wise total salary as shown below

      EMPNO DEPTNO TOT_SAL
      ---------- ---------- ----------
      7934
      7782
      7839
      Total for deptno 10 = 8750
      7902
      7876
      7566
      7369
      7788
      Total for deptno 20 = 10875

      i want to use model clause and get the above result