Home » SQL & PL/SQL » SQL & PL/SQL » Sum() in DENSE_RANK()
Sum() in DENSE_RANK() [message #199834] Thu, 26 October 2006 07:18 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
I am knew to oracle please help me in this query

These are EMP and DEPT tables in scott

SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

so i used this to find Top 5 gr8st salaries in each dept


select * from (select dense_rank()over(partition by d.deptno order by e.sal desc) Rank,d.dname NAME,e.sal SAL from emp e,dept d where e.DEPTNO=d.DEPTNO) where Rank<=5 which gives
this result:--


RANK NAME SAL
-------- -------------- ----------
1 ACCOUNTING 5000
2 ACCOUNTING 2450
3 ACCOUNTING 1300
1 RESEARCH 3000
1 RESEARCH 3000
2 RESEARCH 2975
3 RESEARCH 1100
4 RESEARCH 800
1 SALES 2850
2 SALES 1600
3 SALES 1500

RANK NAME SAL
-------- -------------- ----------
4 SALES 1250
4 SALES 1250
5 SALES 950



now i want sum of these salaries as an extra column including two columns [DEPTNAME,SAL] like SUM(SAL)

for example like this

NAME SAL SUM(SAL)
-------- -------------- ---------- -------------
ACCOUNTING 5000 5000
ACCOUNTING 2450 7450
ACCOUNTING 1300 8750


Re: Sum() in DENSE_RANK() [message #199839 is a reply to message #199834] Thu, 26 October 2006 07:29 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

is this what you are looking for
SELECT DEPTNO,ENAME,DNAME,SAL,CUM_SAL FROM
    (SELECT E.DEPTNO,E.ENAME,D.DNAME,D.LOC,E.SAL,
DENSE_RANK()OVER (PARTITION BY E.DEPTNO ORDER BY E.SAL)TOP_EMP,
SUM(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)CUM_SAL
FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO
    )
WHERE TOP_EMP<=2



regards,

[Updated on: Thu, 26 October 2006 07:30]

Report message to a moderator

Re: Sum() in DENSE_RANK() [message #199844 is a reply to message #199839] Thu, 26 October 2006 07:41 Go to previous message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
Exactly thanks
Previous Topic: Tablenames by SQL-Syntax
Next Topic: function is inserting multiple time
Goto Forum:
  


Current Time: Wed Dec 07 10:58:52 CST 2016

Total time taken to generate the page: 0.17852 seconds