SQL*Plus User's Guide and Reference
Release 9.0.1

Part Number A88827-02
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Formatting Query Results, 3 of 6


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,

Keyboard icon
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;

Screen icon
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
           80 Russell                        14000
           80 Partners                       13500
           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000

6 rows selected.

To make this report more useful, you would use BREAK to establish DEPARTMENT_ID as the break column. Through BREAK you could suppress duplicate values in DEPARTMENT_ID 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, breaks occur every time the column value changes. 


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:

Keyboard icon
BREAK ON DEPARTMENT_ID;

For the following query (which is the current query stored in the buffer):

Keyboard icon
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;
Screen icon
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
           80 Russell                        14000
              Partners                       13500
           90 King                           24000
              Kochhar                        17000
              De Haan                        17000

6 rows selected.

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:

Keyboard icon
BREAK ON DEPARTMENT_ID SKIP 1

Now rerun the query:

Keyboard icon
/

Screen icon
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000

           80 Russell                        14000
              Partners                       13500

           90 King                           24000
              Kochhar                        17000
              De Haan                        17000

6 rows selected.

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 character to be generated 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, clear the buffer:

Keyboard icon
CLEAR BUFFER

Type the following:

Keyboard icon
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID, JOB_ID;

Now, to skip a page when the value of changes and one line when the value of changes, enter the following command:

Keyboard icon
BREAK ON  SKIP PAGE ON  SKIP 1

To show that SKIP PAGE has taken effect, create a TTITLE with a page number:

Keyboard icon
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO

Run the new query to see the results:

Screen icon
                                  Page: 1
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000

                                  Page: 2
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

                                  Page: 3
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           90 AD_PRES    King                           24000

              AD_VP      Kochhar                        17000
                         De Haan                        17000

6 rows selected.

Listing and Removing Break Definitions

Before continuing, turn off the top title display without changing its definition:

Keyboard icon
TTITLE OFF

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

Keyboard icon
BREAK

You can remove the current break definition by entering the CLEAR command with the BREAKS clause:

Keyboard icon
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. For more information about the COMPUTE command, see the "Command Reference" in Chapter 8.

The following table lists compute functions and their effects

Table 4-1 Compute Functions
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. 

The function you specify in the COMPUTE command applies to all columns you enter after OF 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 in Chapter 8 for more details.

Example 4-12 Computing and Printing Subtotals

To compute the total of SALARY by department, first list the current BREAK definition:

Keyboard icon
BREAK

which displays current BREAK definitions:

Screen icon
break on DEPARTMENT_ID page  nodup
          on JOB_ID skip 1 nodup

Now enter the following COMPUTE command and run the current query:

Keyboard icon
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
 /

Screen icon
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000

************* **********                           ----------
sum                                                     13000

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

************* **********                           ----------
sum                                                     27500

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           90 AD_PRES    King                           24000

              AD_VP      Kochhar                        17000
                         De Haan                        17000

************* **********                           ----------
sum                                                     58000

6 rows selected.

To compute the sum of salaries for departments 10 and 20 without printing the compute label:

Keyboard icon
COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY SKIP 1;
SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

Screen icon
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
                                        ----------
                                             13000

           80 Russell                        14000
           80 Partners                       13500
                                        ----------
                                             27500

           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000
                                        ----------
                                             58000


6 rows selected.To compute the salaries just at the end of the report:

Keyboard icon
COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY;
SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

Screen icon
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
           80 Russell                        14000
           80 Partners                       13500
           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000
                                        ----------
                                             98500

6 rows selected.


Note:

The format of the column SALARY controls the appearance of the sum of SALARY, as well as the individual values of SALARY. 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 sales people and change the compute label, first enter the following BREAK and COMPUTE commands:

Keyboard icon
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT

Next, enter and run a new query:

Keyboard icon
SELECT LAST_NAME, SALARY 
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';

Screen icon
LAST_NAME                     SALARY
------------------------- ----------
Russell                        14000
Partners                       13500
Errazuriz                      12000
Cambrault                      11000
Zlotkey                        10500
                          ----------
TOTAL                          61000

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 sales people, first enter the following COMPUTE command:

Keyboard icon
COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT

You do not have to enter a BREAK command; the BREAK you entered in Example 4-13 is still in effect. Now, change the first line of the select query to include COMMISSION_PCT:

Screen icon
Keyboard icon
1 
  1* SELECT LAST_NAME, SALARY
APPEND , COMMISSION_PCT;

Finally, run the revised query to see the results:

Keyboard icon
/

Screen icon
LAST_NAME                     SALARY COMMISSION_PCT
------------------------- ---------- --------------
Russell                        14000             .4
Partners                       13500             .3
Errazuriz                      12000             .3
Cambrault                      11000             .3
Zlotkey                        10500             .2
                          ---------- --------------
sum                            61000            1.5

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 OF 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:

Keyboard icon
BREAK ON DEPARTMENT_ID
COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID

Now, enter and run the following query:

Keyboard icon
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 30
ORDER BY DEPARTMENT_ID, SALARY;

Screen icon
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
              Himuro                          2600
              Tobias                          2800
              Baida                           2900
              Khoo                            3100
              Raphaely                       11000
*************                           ----------
avg                                           4150
sum                                          24900

6 rows selected.

Listing and Removing COMPUTE Definitions

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

Keyboard icon
COMPUTE

Example 4-16 Removing COMPUTE Definitions

To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:

Screen iconKeyboard icon
CLEAR BREAKS
breaks cleared
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.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback