The column you specify in a BREAK command is called a break column. By including the break column in your ORDER BY clause, you create meaningful subsets of records in your output. You can then add formatting to the subsets within the same BREAK command, and add a summary line (containing totals, averages, and so on) by specifying the break column in a COMPUTE command.
For example, the following query, without BREAK or COMPUTE commands,
SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;
produces the following unformatted results:
DEPTNO ENAME SAL
-------- ---------- ---------
10 CLARK 2450
10 MILLER 1300
20 SMITH 800
20 ADAMS 1100
30 ALLEN 1600
30 JAMES 950
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250To make this report more useful, you would use BREAK to establish DEPTNO as the break column. Through BREAK you could suppress duplicate values in DEPTNO and place blank lines or begin a new page between departments. You could use BREAK in conjunction with COMPUTE to calculate and print summary lines containing the total (and/or average, maximum, minimum, standard deviation, variance, or count of rows of) salary for each department and for all departments.
BREAK ON break_column
Note: Whenever you specify a column or expression in a BREAK command, use an ORDER BY clause specifying the same column or expression. If you do not do this, the breaks may appear to occur randomly.
Example 4-9 Suppressing Duplicate Values in a Break Column
To suppress the display of duplicate department numbers in the query results shown above, enter the following commands:
SQL> BREAK ON DEPTNO SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;
SQL*Pus displays the following output:
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
30 ALLEN 1600
JAMES 950
TURNER 1500
WARD 1250
MARTIN 1250
BREAK ON break_column SKIP n
To skip a page, use the command in this form:
BREAK ON break_column SKIP PAGE
Example 4-10 Inserting Space when a Break Column's Value Changes
To place one blank line between departments, enter the following command:
SQL> BREAK ON DEPTNO SKIP 1
Now rerun the query:
SQL> /
SQL*Plus displays the results:
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
30 ALLEN 1600
JAMES 950
TURNER 1500
WARD 1250
MARTIN 1250
BREAK ON ROW SKIP n
To skip a page after every row, use
BREAK ON ROW SKIP PAGE
Note: SKIP PAGE does not cause a physical page break unless you have also specified NEWPAGE 0.
Example 4-11 Combining Spacing Techniques
First, add another column to the current query:
SQL> L 1 SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4* ORDER BY DEPTNO SQL> 1 SELECT DEPTNO, JOB, ENAME, SAL SQL> 4 ORDER BY DEPTNO, JOB
Now, to skip a page when the value of DEPTNO changes and one line when the value of JOB changes, enter the following command:
SQL> BREAK ON DEPTNO SKIP PAGE ON JOB SKIP 1
To show that SKIP PAGE has taken effect, create a TTITLE with a page number, enter
SQL> TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
Run the new query to see the results:
SQL> /
Page: 1
DEPTNO JOB ENAME SAL
---------- --------- ---------- ----------
10 CLERK MILLER 300
MANAGER CLARK 2450
Page: 2
DEPTNO JOB ENAME SAL
---------- --------- ---------- ----------
20 CLERK SMITH 800
ADAMS 1100
Page: 3
DEPTNO JOB ENAME SAL
---------- --------- ---------- ----------
30 CLERK JAMES 950
SALESMAN ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
BREAK
You can remove the current break definition by entering the CLEAR command with the BREAKS clause:
CLEAR BREAKS
You may wish to place the command CLEAR BREAKS at the beginning of every command file to ensure that previously entered BREAK commands will not affect queries you run in a given file.
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column
You can include multiple break columns and actions, such as skipping lines in the BREAK command, as long as the column you name after ON in the COMPUTE command also appears after ON in the BREAK command. To include multiple break columns and actions in BREAK when using it in conjunction with COMPUTE, use these commands in the following forms:
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 COMPUTE function LABEL label_name OF column column column ... ON break_column_2
The COMPUTE command has no effect without a corresponding BREAK command.
You can COMPUTE on NUMBER columns and, in certain cases, on all types of columns. See COMPUTE![[*]](jump.gif)
for details.
The following table lists compute functions and their effects:
| Function | Effect |
| SUM | Computes the sum of the values in the column. |
| MINIMUM | Computes the minimum value in the column. |
| MAXIMUM | Computes the maximum value in the column. |
| AVG | Computes the average of the values in the column. |
| STD | Computes the standard deviation of the values in the column. |
| VARIANCE | Computes the variance of the values in the column. |
| COUNT | Computes the number of non-null values in the column. |
| NUMBER | Computes the number of rows in the column. |
| Table 4 - 1. Compute Functions | |
Labels for ON REPORT and ON ROW computations appear in the first column; otherwise, they appear in the column specified in the ON clause.
You can change the compute label by using COMPUTE LABEL. If you do not define a label for the computed value, SQL*Plus prints the unabbreviated function keyword.
The compute label can be suppressed by using the NOPRINT option of the COLUMN command on the break column. See the COMPUTE command for more details.
Example 4-12 Computing and Printing Subtotals
To compute the total of SAL by department, first list the current BREAK definition:
SQL> BREAK
break on DEPTNO skip 0 page nodup
on JOB skip 1 nodupNow enter the following COMPUTE command and run the current query:
SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> /
SQL*Plus displays the following output:
DEPTNO JOB ENAME SAL
---------- --------- ---------- ----------
10 CLERK MILLER 1300
MANAGER CLARK 2450
********** ********* ----------
sum 3750
DEPTNO JOB ENAME SAL
---------- --------- ---------- ----------
20 CLERK SMITH 800
ADAMS 1100
********** ********* ----------
sum 1900
DEPTNO JOB ENAME SAL
---------- --------- ---------- ----------
30 CLERK JAMES 950
SALESMAN ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
********** ********* ----------
sum 6550To compute the sum of salaries for departments 10 and 20 without printing the compute label:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SKIP 1 SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
----------
8750
20 JONES 2975
20 FORD 3000
20 SMITH 800
20 SCOTT 3000
20 ADAMS 1100
----------
10875To compute the salaries at the end of the report:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 JONES 2975
20 FORD 3000
20 SMITH 800
20 SCOTT 3000
20 ADAMS 1100
----------
19625Note: The format of the column SAL controls the appearance of the sum of SAL, as well as the individual values of SAL. When you establish the format of a NUMBER column, you must allow for the size of sums you will include in your report.
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORT
Example 4-13 Computing and Printing a Grand Total
To calculate and print the grand total of salaries for all salesmen and change the compute label, first enter the following BREAK and COMPUTE commands:
SQL> BREAK ON REPORT SQL> COMPUTE SUM LABEL TOTAL OF SAL ON REPORT
Next, enter and run a new query:
SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE JOB = 'SALESMAN';
SQL*Plus displays the results:
ENAME SAL ---------- -------- ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ********** -------- TOTAL 5600
To print a grand total (or grand average, grand maximum, and so on) in addition to subtotals (or sub-averages, and so on), include a break column and an ON REPORT clause in your BREAK command. Then, enter one COMPUTE command for the break column and another to compute ON REPORT:
BREAK ON break_column ON REPORT COMPUTE function LABEL label_name OF column ON break_column COMPUTE function LABEL label_name OF column ON REPORT
Example 4-14 Computing the Same Type of Summary Value on Different Columns
To print the total of salaries and commissions for all salesmen, first enter the following COMPUTE command:
SQL> COMPUTE SUM OF SAL COMM ON REPORT
You do not have to enter a BREAK command; the BREAK you entered in Example 4-13 is still in effect. Now, add COMM to the current query:
SQL> 1 SELECT ENAME, SAL, COMM
Finally, run the revised query to see the results:
SQL> / ENAME SAL COMM ---------- -------- ---------- ALLEN 1600 300 WARD 1250 500 MARTIN 1250 1400 TURNER 1500 0 ********** -------- ---------- sum 5600 2200
You can also print multiple summary lines on the same break column. To do so, include the function for each summary line in the COMPUTE command as follows:
COMPUTE function LABEL label_name function LABEL label_name function LABEL label_name ... OF column ON break_column
If you include multiple columns after OFF and before ON, COMPUTE calculates and prints values for each column you specify.
Example 4-15 Computing Multiple Summary Lines on the Same Break Column
To compute the average and sum of salaries for the sales department, first enter the following BREAK and COMPUTE commands:
SQL> BREAK ON DEPTNO SQL> COMPUTE AVG SUM OF SAL ON DEPTNO
Now, enter and run the following query:
SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO = 30 4 ORDER BY DEPTNO, SAL;
SQL*Plus displays the results:
DEPTNO ENAME SAL
---------- ---------- ----------
30 JAMES 950
WARD 1250
MARTIN 1250
TURNER 1500
ALLEN 1600
BLAKE 2850
********** ----------
avg 1566.66667
sum 9400
COMPUTE
You can remove all the COMPUTE definitions by entering the CLEAR command with the COMPUTES clause.
Example 4-16 Removing COMPUTE Definitions
To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:
SQL> CLEAR BREAKS breaks cleared SQL> CLEAR COMPUTES computes cleared
You may wish to place the commands CLEAR BREAKS and CLEAR COMPUTES at the beginning of every command file to ensure that previously entered BREAK and COMPUTE commands will not affect queries you run in a given file.