2 Replies Latest reply on Jul 20, 2016 9:00 AM by Stew Ashton

# total sum with pivot

Hi Masters,

how can I get total sum salary for each desg and each dept using pivot in oracle. I am using oracle11.2.

select * from (select deptno,job,sal from emp)

pivot(sum(sal) for deptno in (10,20,30));

How can I get total sum value row wise and colum wise.. Please advise...

JOB                10     20     30       Total

 CLERK 1300 1900 950 SALESMAN 5600 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000

Total

Regards

Ar

• ###### 1. Re: total sum with pivot

Dear 874273,

Something like thiss..

with your_table(dept_no, job, salary) as (

select 10, 'clerk', 1000 from dual union all

select 10, 'clerk', 2000 from dual union all

select 10, 'salesman', 1000 from dual union all

select 10, 'president', 1000 from dual union all

select 10, 'manager', 1000 from dual union all

select 10, 'analyst', 1000 from dual union all

select 20, 'clerk', 1000 from dual union all

select 20, 'clerk', 1000 from dual union all

select 30, 'manager', 1000 from dual )

select * from (

select a.dept_no, a.job, a.salary,

(select sum(salary) from your_table where job=a.job) total

from your_table a )

pivot(

sum(salary) for dept_no in (10, 20, 30) )

Thanks and Regards,

Vysakh Suresh

1 person found this helpful
• ###### 2. Re: total sum with pivot

You want several totals and subtotals:

1. Subtotals by deptno and job (you have this already)
2. Subtotals by deptno
3. Subtotals by job
4. Grand total.

You can get all 4 at once using CUBE:

```select deptno, job, sum(sal) sal
from scott.emp
group by cube(deptno, job);

```

DEPTNOJOBSAL
29025
CLERK4150
ANALYST6000
MANAGER8275
SALESMAN5600
PRESIDENT5000
108750
10CLERK1300
10MANAGER2450
10PRESIDENT5000
2010875
20CLERK1900
20ANALYST6000
20MANAGER2975
309400
30CLERK950
30MANAGER2850
30SALESMAN5600

Now, in order to PIVOT you need to replace the null DEPTNO by some non-null value:

```select * from (
select nvl(deptno, -1) deptno, job, sum(sal) sal
from scott.emp
group by cube(deptno, job)
)
pivot(
sum(sal) for deptno in (10, 20, 30, -1 as TOTAL)
)
order by job;

```

JOB102030TOTAL
ANALYST60006000
CLERK130019009504150
MANAGER2450297528508275
PRESIDENT50005000
SALESMAN56005600
875010875940029025

P.S. I posted a slightly improved solution, with an explanation, on my blog:

https://stewashton.wordpress.com/2016/07/18/spreadsheet-like-totals-and-subtotals/