Oracle SQL - SQLPLUS COMPUTE Command

Introduction

The COMPUTE command can be used with BREAK to give summary results.

COMPUTE allows us to calculate an aggregate value and place the result at the break point.

The syntax of COMPUTE is:

COMPUTE aggregate(column) ON break-point 

To sum the salaries and report the sums at the break points of the above query, we can execute the following script, which contains the COMPUTE command:

Demo

SET echo off 
COLUMN curr_salary FORMAT $9,999,999 -- from www. ja  va  2s.  c o m
COLUMN ename FORMAT a10 
COLUMN region FORMAT a6 

BREAK ON region skip1 
COMPUTE sum of curr_salary ON region 
SET verify off 
SELECT empno, ename, curr_salary, region 
FROM employee 
ORDER BY region 
/ 
CLEAR BREAKS 
CLEAR COMPUTES 
CLEAR COLUMNS 
SET verify on 
SET echo on

While there can be only one BREAK active at a time, the BREAK may contain more than one ON clause.

A common practice is to have the BREAK break not only on some column, but also to have the BREAK be in effect for the entire report.

Related Topics