Home » SQL & PL/SQL » SQL & PL/SQL » How Can I use Sum() Over (Partion By) in Where clause. (Oracle 10.2.0.3.0)
How Can I use Sum() Over (Partion By) in Where clause. [message #363058] Thu, 04 December 2008 23:53 Go to next message
mamalik
Messages: 266
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.

Re: How Can I use Sum() Over (Partion By) in Where clause. [message #363059 is a reply to message #363058] Thu, 04 December 2008 23:55 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
So, what's wrong with the second query? It it too slow or too much typing?
Re: How Can I use Sum() Over (Partion By) in Where clause. [message #363061 is a reply to message #363059] Fri, 05 December 2008 00:11 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR

There is no problem in 2nd query. I want to do it in 1st query.

I want to know it is possible to use "sum() over partition by" to restrict data????.

Regards.
Asif.
Re: How Can I use Sum() Over (Partion By) in Where clause. [message #363071 is a reply to message #363061] Fri, 05 December 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Directly, no. You have to do it using a subquery.
But I think your query is wrong regarding the actual (business) issue you want to solve but you didn't mention.

Regards
Michel
Re: How Can I use Sum() Over (Partion By) in Where clause. [message #363084 is a reply to message #363071] Fri, 05 December 2008 01:09 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Thanks Michel Uncle.

I want to just know that either we can put "sum() over partition by" in where clause or not.

You have answered that we can not put above in where clause directly.


Thanks a lot.

Best Regards.
Asif.
Re: How Can I use Sum() Over (Partion By) in Where clause. [message #363108 is a reply to message #363084] Fri, 05 December 2008 02:26 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
michel is a very smart personality. need not group him in uncle group (which used address for older generation )
Previous Topic: Picture not saved
Next Topic: what is the error here?
Goto Forum:
  


Current Time: Sun Dec 04 19:03:16 CST 2016

Total time taken to generate the page: 0.09819 seconds