How Can I use Sum() Over (Partion By) in Where clause. [message #363058] |
Thu, 04 December 2008 23:53  |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
Dear
I run Following query
Query 1
SELECT empno, ename, deptno, sal, SUM (sal) OVER (ORDER BY deptno, empno) Running_Total
FROM emp
ORDER BY deptno
It Returns
EMPNO ENAME DEPTNO SAL RUNNING_TOTAL
7782 CLARK 10 2450 2450
7839 KING 10 5000 7450
7934 MILLER 10 1300 8750
7369 SMITH 20 16000 24750
7566 JONES 20 2975 27725
7788 SCOTT 20 3000 30725
7876 ADAMS 20 1100 31825
7902 FORD 20 3000 34825
7499 ALLEN 30 1600 36425
7521 WARD 30 1250 37675
7654 MARTIN 30 1250 38925
7698 BLAKE 30 2850 41775
7844 TURNER 30 1500 43275
7900 JAMES 30 950 44225
I want to Get Only row where Running_Total=8750
But i don't want to use follwoing Query.
Query 2.
SELECT *
FROM (SELECT empno, ename, deptno, sal,
SUM (sal) OVER (ORDER BY deptno, empno) running_total
FROM emp
ORDER BY deptno)
WHERE running_total = 8750
May i use SUM (sal) OVER (ORDER BY deptno, empno) In Where Clause. If yes then how.
Thanks In Advance.
Regards.
Muhammad Asif Malik.
|
|
|
|
|
|
|
|