Problem using SQL Rollup clause in Oracle

by Ajay
(India)

Question:

I am using the employees table in the HR schema in the sample Oracle database 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    2450   9400
30     SALESMAN   5600   9400

I used the SQL rollup clause but the results are displayed in a different way. Here is my SQL 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   8750
10     MANAGER    2450   8750
10     PRESIDENT 5000   8750
10                8750   8750
20     ANALYST    6000 10875
20     CLERK      1900 10875
20     MANAGER    2975 10875
20                10875 10875
30     CLERK       950   9400
30     MANAGER    2450   9400
30     SALESMAN   5600   9400
30                8750   9400

Answer:

The problem is that you're using the SQL rollup clause in the sub query - this creates the extra rows and you can't eliminate them in the outer query. What SQL rollup does (as the name suggests) is to roll up (i.e. sum) the results for each of the columns specified. In this case the salaries are summed first by job by department - so for each different job in each department you get a running total - then, secondly by department - to produce a running total for all jobs in each department - and finally a grand total of the salaries for all departments.

The solution, therefore, if you don't want the running totals is to not use the SQL rollup clause and instead just use a normal group by clause. You also need 2 sub queries using what are known as in-line views in Oracle, with one sub query for sum(sal) by job and another for sum(sal) by dept, as follows:

select deptno
      ,job
      ,job_sum
      ,dept_sum
from
(
select department_id deptno
      ,job_title job
      ,sum(salary) job_sum
from employees
join jobs using (job_id)
group by department_id
        ,job_title
)
join
(
select department_id deptno
      ,sum(salary) dept_sum
from employees
group by department_id
)
using (deptno)
order by deptno
        ,job

This would give you the results as per your example. However, what you probably want is to display Sumsal as the total for the department. To do that you would need to use the SQL rollup clause like so:

select deptno
      ,nvl(job, '--total--') job
      ,decode(job, null, null, sumsal) sal
      ,decode(job, null, sumsal) sumsal
from
(
select department_id deptno
      ,job_title job
      ,sum(salary) sumsal
from employees
join jobs using (job_id)
group by rollup (department_id, job_title)
)

which would give you results as before plus running totals by department like this:

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