This discussion is archived
0 Replies Latest reply: Jan 24, 2013 1:23 AM by :) RSS

help model clause

:) Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points