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
The question used
SCOTT.EMP as input. The requirement was to sum salaries by
JOB and to display them as follows:
- The white cells with numbers contain subtotals by
- the yellow cells (right hand column) contain subtotals by
- the blue cells (bottom row) contain subtotals by
- and the green cell (bottom right) contains the grand total.
Getting all the totals
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);
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
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:
GROUPING(DEPTNO)is equal to 1, the row contains a
JOBsubtotal (or the grand total) and I have to assign an arbitrary
DEPTNOvalue so I can pivot.
GROUPING(JOB)is equal to 1, the row contains a
DEPTNOsubtotal (or the grand total) so after pivoting I output ‘Total’ in the
JOBcolumn 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;