SQL & PL/SQL

Hello, I am trying to create a report on staff who are paid above average in the respective departments, each department has an ID, I can calculate the department ID average just fine. I use:

SELECT  department_id 
               AVG(salary)
FROM employee
GROUP BY department_id;
 
and get:


DEPARTMENT_ID AVG(SALARY)
------------- -----------
           10  2916.66667
           12     2418.75
           13        1470
           14        2200
           20        2175
           23        1500
           24        1800
           30  1566.66667
           34        1850
           43        1300

but...What I want to be able to do print out names of staff who are above average for there department, so I need to find the average for that department and then say if employee salart > average then add to report. the table looks like this:

EMPLOYEE_ID LAST_NAME       FIRST_NAME      M     JOB_ID MANAGER_ID HIRE_DATE     SALARY COMMISSION DEPARTMENT_ID
----------- --------------- --------------- - ---------- ---------- --------- ---------- ---------- -------------
       7369 SMITH           JOHN            Q        667       7902 17-DEC-84        800                       20
       7499 ALLEN           KEVIN           J        670       7698 20-FEB-85       1600        300            30

Any way have tried tells me I am comparing a single quantity against multiple rows, is there another way to do this? I know its probably possible with a correlated sub-query but I can't get my head round the logic.

I am sorry, I am really new to SQL.

TIA

Mike

Message was edited by:
user526807

Message was edited by:
user526807