Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: A Neat and Quick Solution
Hello,
The problem as stated does not make much sense and cannot be solved in integer numbers.
Say, you have 15 people with the same salary in a department. Each person then would be getting 6 and 10/15 % (or 6.66...) of the total. How do you propose to 'distribute evenly' 10 between 15 people so that each of them would get an integer number ?
I think a primary school textbook would be most helpful for whoever came up with the requirement.
Rgds.
"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:48:06 CST