Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    12

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

    First of all, hi to everyone!

    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 lack some understanding on how the avg() works.

    This is my problem...

    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)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT emp.ename
         , emp.sal
         , dept.dname
         , aaa.avg_sal
      FROM emp
    INNER
      JOIN dept 
        ON dept.deptno = emp.deptno 
    INNER
      JOIN ( SELECT deptno
                  , AVG(sal)  avg_sal
               FROM emp
             GROUP 
                 BY deptno ) aaa
        ON aaa.deptno = emp.deptno
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    Thank you, r937. This is a big help. If you have the time, I would also like to understand how and why it works.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Michael,
    This is pretty basic stuff, you may want to look up one of the SQL cookbooks or learning SQL. Your original query was asking for an average salary of each employee in each dept. Which may have given you the same value as the salary(depending on whether you keep employees pay history within the table).
    R937 gave you an SQL statement that got each employees info and separately in a nested expression got the AVG salary for each dept, then joined each employees dept to the avg salary for that dept.
    Dave

  5. #5
    Join Date
    Sep 2009
    Posts
    12
    I am finding my SQL knowlege is incomplete. I just learned it on the fly. I will take your advice and get a 'how to' book, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •