SQL*Plus User's Guide and Reference

Contents Index Home Previous Next

Clarifying Your Report with Spacing and Summary Lines

When you use an ORDER BY clause in your SQL SELECT command, rows with the same value in the ordered column (or expression) are displayed together in your output. You can make this output more useful to the user by using the SQL*Plus BREAK and COMPUTE commands to create subsets of records and add space and/or summary lines after each subset.

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           1250

To 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.

Suppressing Duplicate Values in Break Columns

The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:

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

Inserting Space when a Break Column's Value Changes

You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:

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

Inserting Space after Every Row

You may wish to insert blank lines or a blank page after every row. To skip n lines after every row, use BREAK in the following form:

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.

Using Multiple Spacing Techniques

Suppose you have more than one column in your ORDER BY clause and wish to insert space when each column's value changes. Each BREAK command you enter replaces the previous one. Thus, if you want to use different spacing techniques in one report or insert space after the value changes in more than one ordered column, you must specify multiple columns and actions in a single BREAK command.

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

Listing and Removing Break Definitions

You can list your current break definition by entering the BREAK command with no clauses:

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.

Computing Summary Lines when a Break Column's Value Changes

If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:

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[*] 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
The function you specify in the COMPUTE command applies to all columns you enter after OFF and before ON. The computed values print on a separate line when the value of the ordered column changes.

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 nodup

Now 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                                   6550

To 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
                      ----------
                           10875

To 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
                      ----------
                           19625

Note: 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.

Computing Summary Lines at the End of the Report

You can calculate and print summary lines based on all values in a column by using BREAK and COMPUTE in the following forms:

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

Computing Multiple Summary Values and Lines

You can compute and print the same type of summary value on different columns. To do so, enter a separate COMPUTE command for each column.

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

Listing and Removing COMPUTE Definitions

You can list your current COMPUTE definitions by entering the COMPUTE command with no clauses:

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.


Contents Index Home Previous Next