COMPUTE calculates an aggregate value and place the result at the break point : Break « SQL PLUS Session Environment « Oracle PL/SQL Tutorial






COMPUTE calculates an aggregate value and place the result at the break point.

The syntax of COMPUTE is:

COMPUTE aggregate(column) ON break-point

SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060
725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860
221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900
315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990
421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980
808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960
104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980
212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020
415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer

02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver

04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager

05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York

07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager

08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester


8 rows selected.

SQL>
SQL>
SQL> COLUMN City FORMAT a12
SQL> BREAK ON City skip1
SQL> COMPUTE sum of salary ON City
SQL> SELECT *
  2  FROM employee
  3  ORDER BY city
  4  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY         DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------------ ---------------
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York     Manager
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78              Tester
                                               ---------- ************
                                                 12220.56 sum

01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto      Programmer
                                               ---------- ************
                                                  1234.56 sum

05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver    Tester
08   James      Cat        17-SEP-96 15-APR-02    1232.78              Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78              Tester
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78              Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78              Manager
                                               ---------- ************
                                                  19118.9 sum


8 rows selected.

SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR COLUMNS
columns cleared
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>








29.6.Break
29.6.1.Break the report on some column to produce easy-to-read output
29.6.2.If a blank line is desired: BREAK ON columnName skip1
29.6.3.An ORDER BY clause that mirrors the BREAK command
29.6.4.Two Break commands
29.6.5.COMPUTE calculates an aggregate value and place the result at the break point
29.6.6.BREAK may contain more than one ON clause.
29.6.7.break on department skip 1 on job
29.6.8.CLEAR BREAKS
29.6.9.Break on two