A Neat and Quick Solution

From: CK <emailckn_at_yahoo.com>
Date: 24 Nov 2003 21:03:15 -0800
Message-ID: <b5123dfd.0311242103.2cd071c1@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 Mon Nov 24 2003 - 23:03:15 CST