Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task.

Example: Show percentage of salaries for each department

Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.

column percentage format 99.9

select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;

DEPTNO SUM(SAL) PERCENTAGE
---------- ---------- ----------
10 8750 30.1
20 10875 37.5
30 9400 32.4

With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:

column percentage format 99.9

select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;

DEPTNO SUM(SAL) PERCENTAGE
---------- ---------- ----------
10 8750 30.1
20 10875 37.5
30 9400 32.4

The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.