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,

• Paul Dong