Re: A Neat and Quick Solution

i'm guessing that you're asking how to distribute the difference

looks like you're adept at from-clause subquerys -- include one to calculate the rounding difference and return the appropriate value to get an even distribution

• mcs

"CK" <emailckn_at_yahoo.com> wrote in message news:b5123dfd.0311242103.2cd071c1_at_posting.google.com...
| Consider a table EMP with the following structure
|
| EMP
| ------------
| emp_id
| dept_id
| emp_name
| salary
|
| I need to run a query, that will list out employees by each dept_id
| and reports the percentage of salary drawn with respect to the
| department he works for. There is one condition though, the %age needs
| to be rounded to the nearest integer value and the sum of all salary
| percentages for all the employees for the department should be equal
| to 100%.
|
| select a.emp_id, a.dept_id, a.salary, b.dept_salary,
| round((a.salary/(b.dept_salary))*100),0) pctage
| from emp a, (select dept_id, sum(salary) dept_salary from emp group by
| dept_id) dept_salary b
| where a.dept_id = b.dept_id
|
|
| The above query returns the salary percentages for each employee wrt
| to the department he works for. However, a visual glance of the
| records tells me that the sum of the %ages is > 100.
|
| What is acceptable?
| If the sum of the %ages exceeds 100, you can distribute it evenly
| among all the employee salary pctages.
Received on Tue Nov 25 2003 - 07:02:40 CST