Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

COMPUTE function: How to get subtotals and GRAND total together?

705974Jan 26 2010 — edited Jan 27 2010
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?
This post has been answered by Frank Kulash on Jan 26 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 24 2010
Added on Jan 26 2010
8 comments
27,961 views