I am wrtiting a report using SQL and SQL*Plus to get subtotals and a grand total. The COMPUTE function allows me to COMPUTE SUM on a group, but it only gives me subtotals. I'm interested in getting a report with the SUBTOTALS and a final GRAND TOTAL. Can this be done using SQL*Plus?
Here is my current code that gives me subtotals:
COMPUTE SUM LABEL subtotal OF sal ON deptno
SELECT ename, sal, deptno
FROM emp
ORDER BY deptno;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
VOLLMAN 5000
MILLER 1300
---------- **********
8750 subtotal
SMITH 800 20
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
---------- **********
10875 subtotal
ALLEN 1600 30
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
---------- **********
9400 subtotal
Here is the code to give one grand total:
Column DUMMY NOPRINT
COMPUTE SUM OF sal ON DUMMY
BREAK ON DUMMY
SELECT NULL DUMMY, ename, sal, deptno
FROM emp
ORDER BY deptno;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
VOLLMAN 5000 10
MILLER 1300 10
SMITH 800 20
ADAMS 1100 20
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
ALLEN 1600 30
BLAKE 2850 30
MARTIN 1250 30
JAMES 950 30
TURNER 1500 30
WARD 1250 30
----------
29025
How can I combine both on one report?