Formatting SQL*Plus Reports Part 2

Computing Summary of a column
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