# Spreadsheet-like Totals and Subtotals

We very often make spreadsheets with subtotals for each row and for each column. Someone on the OTN forum asked how to produce data in this format. I answered using the cool `CUBE` function.

The question used `SCOTT.EMP` as input. The requirement was to sum salaries by `DEPTNO` and `JOB` and to display them as follows:

JOB 10 20 30 TOTAL
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
Total 8750 10875 9400 29025

• The white cells with numbers contain subtotals by `DEPTNO` and `JOB`;
• the yellow cells (right hand column) contain subtotals by `JOB`;
• the blue cells (bottom row) contain subtotals by `DEPTNO`;
• and the green cell (bottom right) contains the grand total.

#### Getting all the totals

The `CUBE` extension to `GROUP BY` is ideal for this kind of cross-tabular report: it will generate everything we need with one `SELECT` and one table scan.

```select deptno, job, sum(sal) sal
from scott.emp
group by cube(deptno, job);```
DEPTNO JOB SAL
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

#### Formatting the output

Some folks whose opinion I respect say that formatting reports should be done outside of SQL. I agree in principle, but that didn’t stop me from answering the question using the `PIVOT` clause. As always with this clause, you have to know in advance how many columns you want to end up with!

The tricky part of this particular pivoting operation is handling NULLs correctly. For one thing, the `JOB` subtotals need to be pivoted to the rightmost column, but they have no `DEPTNO` value to pivot to. For another thing, the input might have NULLs in the `JOB` or `DEPTNO` columns, so I need a reliable way to identify the output rows that have subtotals.

I use the `GROUPING()` function to identify the subtotals:

• When `GROUPING(DEPTNO)` is equal to 1, the row contains a `JOB` subtotal (or the grand total) and I have to assign an arbitrary `DEPTNO` value so I can pivot.
• When `GROUPING(JOB)` is equal to 1, the row contains a `DEPTNO` subtotal (or the grand total) so after pivoting I output ‘Total’ in the `JOB` column of the last row.
```select case gr_job when 1 then 'Total' else job end job,
"10", "20", "30", "Total"
from (
select case grouping(deptno) when 1 then -1 else deptno end deptno,
job, grouping(job) gr_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 gr_job, job;```
JOB 10 20 30 Total
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
Total 8750 10875 9400 29025