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

[M Burke]

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é]

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 ?:

SELECT Dept.Deptno, avg(emp.sal)
FROM Dept, Emp
WHERE Emp.deptno = Dept.deptno 
GROUP BY Dept.Deptno

Once you've got that, how would you do 2. ?