Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL Question (percentage query)
Re: SQL Question (percentage query)
>
> Let's say I have a table with two columns: dept#, and number of employees
> in each department. The table looks as follow:
> Dept# Number of Employees
> 10 60
> 20 70
> 30 80
> I would like to create a query that returns the percentage the
> number of employees in each department represents of the total
> number of employees. The result will look like:
> Dept# %
> 10 28.57
> 20 33.33
> 30 38.09
> Any help would be appreciated.
> Thx. Iraida
>
Iraida,
I agree with Gary; Oracle will need to make two passes at your department table. There are a couple of ways to get the divisor for your percentage without pl/sql, but they have their pluses and minuses. I'm going to leave out the percentage formatting, since previous messages have handled that.
(1) two references to your department table in the select, i.e.:
SELECT d1.dept, d1.num_of_emps/SUM(d2.num_of_emps)
FROM department d1, department d2
GROUP BY d1.dept, d1.num_of_emps;
The downside to this one is that it is extremely inefficient, since if requires a Cartesian Product before grouping and summing. You would never want to do this with anything but a very small table.
(2) use a view to encapsulate the total number of employees, i.e.,
CREATE OR REPLACE VIEW department_totals AS
SELECT SUM(num_of_emps) emp_count
FROM department;
SELECT d.dept, d.num_of_emps/dt.emp_count FROM department d, department_totals dt;
The upside to this approach are:
(a) you get read consistency without locking the table, i.e., num_of_emps will not change between the summing and the percentage calculation, since they are part of the same query.
(b) this approach can be used with read-only query tools that cannot handle the procedural logic required by submitting two queries.
The downside to this approach is that tuning the query is more complicated than Gary's solution. Ideally, you want to the summing operation to happen once. It is possible for a pure-sql solution to calculate the sum repetitively, once for each row in the department table. Obviously, this is what you don't want. The desirable query plan is reached using the rule-based optimizer when the view appears last in the from-clause, or using the cost-based optimizer with the /*+ ORDERED */ hint and the view appearing first in the from-clause.
Hope this helps,
![]() |
![]() |