wood burning stoves 2.0*
The moose likes Oracle/OAS and the fly likes Finding an average as part of a join (Ora 11) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Finding an average as part of a join (Ora 11)" Watch "Finding an average as part of a join (Ora 11)" New topic
Author

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

M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
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

Joined: Nov 24, 2005
Posts: 14687
    
  16

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


[My Blog]
All roads lead to JavaRanch
 
 
subject: Finding an average as part of a join (Ora 11)