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

    total sum with pivot

    874273

      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

      CLERK13001900950
      SALESMAN5600
      PRESIDENT5000
      MANAGER245029752850
      ANALYST6000

      Total              

       

      Regards

      Ar

        • 1. Re: total sum with pivot
          Vysakh Suresh - 3035408

          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
            Stew Ashton

            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/