I have two data tables Employees and Departments. I want to show the number of employees in each department, the average of salaries in each department, among other things. The code I have is:
select d.department_id, d.department_name, count(e.last_name) Employees,
avg(e.salary) Avg_Salary, e.last_name, e.salary, e.job_id
from departments d, employees e
where d.department_id = e.department_id
group by d.department_id, d.department_name, e.last_name, e.salary, e.job_id
order by d.department_id;
This code runs fine, but the results for the employee count and average salary are not what I am looking for. My results are:
Department_ID Department_Name Employees Avg_Salary Last_Name Salary Job_ID
------------- --------------- --------- ---------- --------- ------ ------
10 Administration 1 4400 Whalen 4400 AD_ASST
20 Marketing 1 6000 Fay 6000 MK_REP
20 Marketing 1 13000 Hartstein 13000 MK_MAN
As you can see, the Employees count display 1 for each employee, not the total in each department; and the Avg_Salary is the same as Salary, not the actual salary average in each department.
This is the results I want:
Department_ID Department_Name Employees Avg_Salary Last_Name Salary Job_ID
------------- --------------- --------- ---------- -------- ------ ------
10 Administration 1 4400 Whalen 4400 AD_ASST
20 Marketing 2 9500 Fay 6000 MK_REP
20 Marketing 2 9500 Smith 13000 MK_MAN
How do I fix this?
This should do it - using analytic functions:
select d.department_id, d.department_name,
count(e.last_name) over (partition by d.department_id) Employees,
avg(e.salary) over (partition by d.department_id) Avg_Salary,
e.last_name, e.salary, e.job_id
from departments d, employees e
where d.department_id = e.department_id
order by d.department_id;