Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 9.0.1)
SQL Query [message #402283] Fri, 08 May 2009 07:31 Go to next message
ajay.oracle
Messages: 3
Registered: October 2008
Location: India
Junior Member
Hi Gurus,


I am taking the example of emp table and i want the data to display in this format


Deptno Job Sal Sumsal
10 CLERK 1300 8750
10 MANAGER 2450 8750
10 PRESIDENT 5000 8750
20 ANALYST 6000 10875
20 CLERK 1900 10875
20 MANAGER 2975 10875
30 CLERK 950 9400
30 MANAGER 2850 9400
30 SALESMAN 5600 9400



i used rollup but it displayed in different way


here is the query


select deptno, job, sumsal sal,
decode(job, null, sumsal) sumsal
from (
select deptno, job, sum(sal) sumsal from emp group by rollup(deptno,job))


and it displayed as


Deptno Job Sal Sumsal
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 9400


Please help me.

Thanks,

Ajay
Re: SQL Query [message #402285 is a reply to message #402283] Fri, 08 May 2009 07:42 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You'll want to look at the following analytic:

SUM
OVER()
ORDER BY
Previous Topic: Instead of CONTINUE
Next Topic: Trigger Error
Goto Forum:
  


Current Time: Sun Dec 11 00:25:50 CST 2016

Total time taken to generate the page: 0.09066 seconds