Friday, August 15, 2008

Sub and Grand Totals


Clear Screen
Column Comm Format 99,999
Column Sal Format 99,999
Column Deptno Format 99 New_Value xDeptNo Noprint
Column Dname Format A13
Column Job Format A10
Set Newpage 2 Pagesize 60
Clear Computes
Clear Breaks

---------------------------------------------------------------------------
-- Use a compute for each field that you break on for which you want totals
-- Include more than one field in the 'of' without using commas.
----------------------------------------------------------------------------
Compute Min Label 'Dpt Min $' -
Max Label 'Dpt Max $' Of Comm Sal On Dname

Compute Number Label 'Count' -
Sum Label 'Total $' Of Comm Sal On Job

Compute Count Label 'Tot Not Null #' -
Number Label 'Tot Row Count' -
Avg Label 'Tot Average $' -
Sum Label 'Tot Sum $' Of Comm Sal On Report

---------------------------------------------------------------------------
-- Mention those fields in the break statement
----------------------------------------------------------------------------
Break On Deptno Skip Page -
On Dname Nodup -
On Job Skip 1 Dup -
On Report Skip Page

---------------------------------------------------------------------------
-- Make sure they're sorted correctly
----------------------------------------------------------------------------
Select Dname, Deptno, Job, Ename, Comm, Sal
From Emp
Join Dept
Using (Deptno)
Where DeptNo in (20,30)
And Job ^= 'MANAGER'
Order By Deptno, Job;

No comments:

Post a Comment