With combination of BREAK and COMPUTE command you can do several calculations on SQL reports using sql*plus.
Remember the COMPUTE command has no effect without a corresponding BREAK command.
Below is the lists of compute functions of Sql*plus and their effects.
1)SUM: Sum of the values in the column.
2)MINIMUM: Minimum value in the column.
3)MAXIMUM: Maximum value in the column.
4)AVG: Average of the values in the column.
5)STD: Standard deviation of the values in the column.
6)VARIANCE: Variance of the values in the column.
7)COUNT: Number of non-null values in the column.
8)NUMBER: Number of rows in the column.
Let's look at our data,
SQL> select dept_id,name,salary from emp;
DEPT_ID NAME SALARY
---------- ------------ ----------
10 SHAIK 60000
10 ABDUL 22000
10 KHALEEL 40000
20 IBRAHIM 35000
20 RAVI 60000
30 KASARAPU 25000
6 rows selected.
Now we wish to compute the total of SALARY by department. To do that use,
SQL> break on dept_id
SQL> compute sum of salary on dept_id
SQL> /
DEPT_ID NAME SALARY
---------- ------------ ----------
10 SHAIK 60000
ABDUL 22000
KHALEEL 40000
********** ----------
sum 122000
20 IBRAHIM 35000
RAVI 60000
********** ----------
sum 95000
30 KASARAPU 25000
********** ----------
sum 25000
6 rows selected.
Note that the word sum appears in every break. If you don't want to print sum word then do as,
SQL> COLUMN DUMMY NOPRINT;
SQL> COMPUTE SUM OF SALARY ON DUMMY;
SQL> BREAK ON DUMMY SKIP 1;
SQL> select dept_id DUMMY,dept_id,name,salary from emp;
DEPT_ID NAME SALARY
---------- ------------ ----------
10 SHAIK 60000
10 ABDUL 22000
10 KHALEEL 40000
----------
122000
20 IBRAHIM 35000
20 RAVI 60000
----------
95000
30 KASARAPU 25000
----------
25000
6 rows selected.
To compute the salaries just at the end of the report,
SQL> COLUMN DUMMY NOPRINT;
SQL> COMPUTE SUM OF SALARY ON DUMMY;
SQL> BREAK ON DUMMY;
SQL> SELECT NULL DUMMY,DEPT_ID,NAME,SALARY FROM EMP;
DEPT_ID NAME SALARY
---------- ------------ ----------
10 SHAIK 60000
10 ABDUL 22000
10 KHALEEL 40000
20 IBRAHIM 35000
20 RAVI 60000
30 KASARAPU 25000
----------
242000
6 rows selected.
To calculate grand total of salary and make it a level do,
SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT
SQL> select dept_id,name,salary from emp;
DEPT_ID NAME SALARY
---------- ------------ ----------
10 SHAIK 60000
10 ABDUL 22000
10 KHALEEL 40000
20 IBRAHIM 35000
20 RAVI 60000
30 KASARAPU 25000
----------
TOTAL 242000
6 rows selected.
To compute the both average and sum of salaries of a department do,
SQL> BREAK ON DEPT_ID
SQL> COMPUTE AVG SUM OF SALARY ON DEPT_ID
SQL> select dept_id,name,salary from emp where dept_id=10;
DEPT_ID NAME SALARY
---------- ------------ ----------
10 SHAIK 60000
ABDUL 22000
KHALEEL 40000
********** ----------
avg 40666.6667
sum 122000
To see current compute settings,
SQL> compute
COMPUTE sum LABEL 'sum' OF SALARY ON DUMMY
COMPUTE sum LABEL 'TOTAL' OF SALARY ON REPORT
COMPUTE avg LABEL 'avg' sum LABEL 'sum' OF SALARY ON DEPT_ID
To remove all computes definitions,
SQL> clear compute
computes cleared
