average employee salary for each department

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

average employee salary for each department

Author

Topic

lovray
Starting Member

9 Posts

Posted - 2009-06-25 : 17:42:00

average employee salary for each department.
Include the Manager's name and Department description.

from these 3 tables:
EMPLOYEE (key:EMP_ID, FIELDS:EMP_NAME,EMP_SALARY),

DEPARTMENT (key:DPT_ID,fields:DPT_DESC, EMP_ID, foreign keys:EMP_ID (department manager’s EMP_ID) )
and

EMPLOYEE_DEPARTMENT(foreign keys:EMP_ID,DPT_ID)

much thanks

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-25 : 17:57:13

Sounds like a homework problem.

Some days you're the dog, and some days you're the fire hydrant.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-25 : 18:14:31

Lovray,

This does look like a homework problem. We'll be glad to help you, but only after you have given it a try. Post what you have so far and we'll get you the rest of the way.

Jim

lovray
Starting Member

9 Posts

Posted - 2009-06-25 : 18:55:47

ok guys you got me. : ). well here's what I have. I think this looks right:

SELECT emp_name,dpt_desc, tempAvg_sal FROM employee e
INNERJOIN department d ON e.emp_id = d.emp_id
INNERJOIN
(SELECT avg(emp_salary) AS tempAvg_sal, dpt_id AS tempDpt_Id
FROM employee temp_e INNERJOIN employee_department temp_ed ON
temp_e.EMP_ID = temp_ed.EMP_ID GROUP BY tempDpt_Id) myTable
ON myTable.tempDpt_Id = department.dpt_Id;

lovray
Starting Member

9 Posts

Posted - 2009-06-25 : 19:01:38

oh, hey do you guys know what it means to creating an object model(of my above work) in c#. Do you know if this means I'm suppose to create an actual object for each table with methods that interface with the DB or something.. Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-25 : 19:36:42

Okay, there's a few things going on with this query. The request is for
average salary by Mgr's name and description, so this is all that should appear in your select statement
SELECT
e.emp_name,d.dpt_desc, AVG(e.salary)
But how to get from employee to mgr. I assume that employee_department is a LINKING table that has mgrs and their employees
so
FROM
department d
INNER JOIN
employee_department ed
ON

d.dpt_id = ed.dpt_id

and the employee table has the salaries we want

INNER JOIN
employee e
ON
d.emp_id = e.emp_id

and since you have to group by everything you aren't aggregating (SUM,AVG,COUNT, etc.)

GROUP BY
e.emp_name,d.dpt_desc

so perhaps this? The trouble is that there is no way to know what the manager's name is! How do can you tell? If you put in the emp_name, all you'll get is the salary for each employee (that's why I left out the emp_name in the final query).

Jim


SELECT 
    d.dpt_desc
 , [AverageSalary] = AVG(e.salary) 
FROM 
    department d 
INNER JOIN
  employee_department ed
ON
  
   d.dpt_id = ed.dpt_id
INNER JOIN 
    employee e   
ON 
   d.emp_id = e.emp_id

GROUP BY
   ,d.dpt_desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources