Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Finding an average as part of a join (Ora 11)

 
M Burke
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What I need to find is the average salary for each department, and print out each employee, there salary, department, and the average salary for there department.

Any Ideas? I think I am missing some understanding on how the avg() works.

The join is on emp department number and a lookup on the dept table.

I think I am close. This is what I have so far. I am finding the department name as part of the lookup. But the avg() is not adding up the department salaries and computing the average.

select emp.ename, emp.sal, dept.dname, avg(emp.sal)
from emp, dept
where emp.deptno = dept.deptno
GROUP BY emp.ename, emp.sal, dept.dname


The column definitions are as follows...
>desc dept

Name Null Type
------------------------------ -------- ---------------------------
DEPTNO NOT NULL NUMBER(4)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

>desc emp

Name Null Type
------------------------------ -------- ------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(4)

 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you break up thing, there are two things you want to do :
1. Get the average salary for each department
2. Get the employee list, with the average salary of its department.

What about 1. ? Wouldn't it look something like this ?:


Once you've got that, how would you do 2. ?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic