SQL & PL/SQL

I need to count number of employees in each department whose salary is greater than Average Salary. I got stuck with this... Any ideas?

Here's my code:

DECLARE
  CURSOR c_1
  IS
    SELECT department.department_id, department_name,
      COUNT(employee_name) employee_name
    FROM department, employee
    WHERE department.department_id = employee.department_id(+)
    GROUP BY department.department_id, department_name
    ORDER BY department_name, employee_name;
  v_prev_dname department.department_name%type := '?';
  v_average_salary VARCHAR2(30);
BEGIN
  DBMS_OUTPUT.PUT_LINE('DEPARTMENT_NAME   NUMB_OF_EMP         AVERAGE_SALARY');
  FOR indx IN c_1
  LOOP
    IF v_prev_dname != indx.department_name THEN
      SELECT NVL(TO_CHAR(ROUND(AVG(salary), 2), 99999.99), 'N/A')
      INTO v_average_salary
      FROM employee
      WHERE department_id = indx.department_id;
      DBMS_OUTPUT.PUT_LINE(RPAD(indx.department_name, 9) || '   ' || LPAD(indx.employee_name,17) || '     ' || LPAD(v_average_salary, 17));
      v_prev_dname := indx.department_name;
    END IF;
  END LOOP;
END;

This is an output:

DEPARTMENT_NAME   NUMB_OF_EMP         AVERAGE_SALARY
ACCOUNTIN                   7               3050.00
OPERATION                   0                   N/A
RESEARCH                    4               1750.00
SALES                       10               7546.55

Edited by: user13675672 on Feb 12, 2011 6:51 PM